Excel Template: Loan Calculator
Printer friendly Excel Spreadsheet for creating a loan amortization schedule. The calculator accommodates loans with up to 40 years (or 480 monthly payment periods).
Download a Free Loan Calculator Spreadsheet for creating a payment schedule and a table showing the summary of principal and interest payments, with optional extra payments.
How Loan Calculator with Extra Payments Works?
Simply enter:
- Loan Amount
- Annual Interest Rate
- Loan Period in Years
- Number of Payments per Year
- Date
Now you can view total, principal and interest payments. Also you can make extra payments each period.
Excel Template: Loan Calculator (260 Kb)
It would be nice if we there was a field for a Name input as well as the ability to change the currency of the loan calculator.
On a different note, I was sad to see (or not see) that you do not have a downloadable spreadsheet for the upcoming T20 World Cup in South Africa.
Regards
I to am a South African and would love to be able to change the currency to ZAR.
as mentioned it is so cool if there was other currency i.e Bahraini Dinar, Kuwaiti Dinar, Saudi Riyal etc ….
Thank U!
It would be nice if we could change the Number of Payments Per Year to 26 (fortnightly) or 52 (weekly).
Thanks
Looks Great
Done.
Now you can change currency of the calculator. You must change formatting of Loan Amount (cell D8). After that you must change value in any cell (for example, Loan Period) to update the worksheet.
Newer version available
It does not calculate payments more than 480 i.e. if 40 years selected and no.of payments 26 per year then it does not work.
Ashok
hey excel trick. this format considers that any extra payments reduces the number of months. can you have an option where instead of reducing the number of months, it reduces the monthly payments instead? (which is my case)
also, my loan’s interests could vary over the years. how can i adjust that on a yearly basis without affecting what i’ve paid in the previous years?
thanks,
Ashok,
Done.
grunberg,
Done. Now you should use sheet “Fixed Number of Payments”, also you can change Interest Rate.
thank you very much
AG
Hi excel trick,
The changes you did for Grunberg was excellent, but can we have the loan period in months, the loan calculator does not except fractions, e.g. 2.5 years.
Much obliged.
Done, enjoy.
How do i get hold of the new file?
Chris,
Loan Calculator
Hi exel trick
I don’t understand, this file still only accept loan periods in years.
It would be nice if grace periods could be included to allow for a loan to be issued one date and accumulate interest as principal until a future date when repayment would start.
Hi Excel Trick,
Thanks for your generosity in sharing with the public.
Nicely done…
hi Excel Trik
great work … but id like to suggest that ur Loan Calculator can accommodate a “final balloon payment” with a fixed number of payment , which therefore adjusts the payment amount (interest and principle) and total interest.
the version i have here makes the adjustment if additional payments were made during the loan only.
looking forward to ur response
thanx
Hi Excel Trik,
Thank you for your excellent loan calculator. I noticed you made a change on the fixed number of payments sheet so that any extra payments reduced the future payment amounts. I am wondering if you can give me the formula, or give an option on the sheet, so that any extra payments do not effect future payments. I know the Loan Calculator sheet does this, however, I can not use it as the rest of the formulas do not do what I need.
Please help,
Thanx
Steve, you can unprotect project sheets and inspect/change all formulas (password is blank).
Help! I need to set up a repayment schedule in Excel and can’t find anything to help…for the following issue:
4,000,0000 loan.
6% annual interest
Annual interest to be paid will be $234000.00
Daily Interest to be paid will be $641.10
We are only paying interest (different amounts depending on the number of days in the month) until we are required to make a principle payment which is on demand.
I need some kind of formula that will help me recalculate the interest payments when the balance of the loan changes…Which it will..
I am a beginner at excel.
Guys,
your formula for interest rate per period is wrong - it yields too high results for repayment
the proper formula for per-period-interest-rate is
=(1+annual_APR)^(1/no_of_periods_per_year)
Sir , could you help me in calculating my loan schedule repayment as my monthly installment are not regular.
For e.g my loan started on 10/17/2007
i made my first payment on 12/10/2007
second on 01/01/2008
third on 01/31/2008
fourth on 02/23/2008
you see repayment is not a fixed date of a particular month, please note my loan is daily reducing basis for 5 years.
Please help me