How to make amortization schedule in excel
Amortization Schedule Calculator
Posted on September 12, 2009
This calculator is one of the most popular calculator that you can find in many internet websites either online or in excel file. Any banks or financial institution usually provide this tool in their websites to ease their customers calculating their loan schemes before deciding to make a loan.
Why this calculator is important. Because of the interest calculation for loan some money from the bank or financial institution is not a simple calculation. Most of the people, like me, in the beginning just knowing that if they loan some money for some period of time, say 5 years, will think that after they pay regularly for 2.5 years, the principal will become half of the money they borrowed. But, the reality is not. They still owe more than half of the money they borrowed. Because the calculation, where it is common in all countries, will make they pay interests first rather than make the principal and interest in equal treatment. And this is some kind of strategy to make they pay their loan until the end. So, you have to carefully calculate and compare any loan schemes before you deciding to have some loan.
Microsoft Excel put many financial formulas in its software. So, you can just use it. And I didn’t anything special in this spreadsheet. I just implement the formula. I made this
to teach my nephew who take finance and accounting school on using excel built-in financial formula to help her understanding loan and mortgage concept.
Basically, the excel built-in formula used here is. PMT(rate,nper,pv,[fv],[type]). Why we are using this formula, because our aim is to find the monthly repayment of the loan, based on constant payment and constant interest rate. I put the the formula term next to the input value in the spreadsheet. You can read the description of those terms in the excel offline help. Since the formula is yearly based, you should divide the “rate” by 12 to get your monthly interest rate, and multipy “nper” by twelve to get your total month of your loan. You can omit the “fv” because the default is “0” which mean the final loan value by the end of your loan period and you can also omit “type” if your payment due is at the end of each month.
Do not make this calculator as your real reference, because some banks or financial institution should apply different calculation method based on their internal policy. Just use this calculator to figure out your loan roughly.
If you want to get more information regarding the concept of amortization, mortgage or other financial term, just googling them… :-). There are many websites that can give you better and detail explanation.
You can download the file here .Source: exceltemplate.net