Frank the Financially Savvy Atheist - The F.S.A.

I showed you how to use the pmt function in excel to calculate the monthly payment for your loans. Now, I'm going to show you how to create an amortization table in excel to figure out exactly how your loan pays down over time.

Why would you want to create your own amortization table instead of using the bevy of financial calculators out there? Three reasons:
1. You can customize your table to suit your needs. See what is your current loan balance. You could even enter in some home appreciation assumptions to see how your equity builds up. This would be important if you are trying to see when you can get rid of PMI payments.
2. Like everything else in life, some people are DIY'ers. For me in particular, I like having amortization tables for all my loans, so I can see where I am each month. It gives me a sense of control, whether it's a false sense or not.
3. It's easy! Trust me.

With that out of the way, let's get started.

First, a little bit about how loans work (in general). Every month you loan balance goes up by interest and down by the amount of principal paid. Because you're charged interest on the loan amount, the principal amount is not reduced by the same amount of your monthly payment. Lets take a look at this in Excel.

We need three inputs: Loan Amount, Interest Rate, and Loan term in months.

Now we need to calculate our monthly payment using the pmt function. Remember, we divide the interest rate by 12 to get a monthly rate. See image below.




From here we mimic what happpens to our loan. We get charged interest on the remaining loan balance, and our monthly payment covers interest and some principal. We take the balance minus the principal repaid too see what the remaining balance is each month. See the image below. I've included the formulas for each cell.



Now, we double-check to make sure we did everything correctly. In month 360 we should have paid off our entire loan. Lo and behold, we did.



That's it. We're done. Now you can customize it to your hearts content, adding equity buildup in homes and look at how making extra payments will affect your loan. I'm assuming most people are already familiar with Excel and know how to copy down formulas and the like. If there are any questions, feel free to leave a comment or send me an email. Hope this was helpful.


Tags: personal finance amortization