The topic of debt can lead to some heated debate within the personal finance community. While some shun it, others see the value in borrowing for certain things, such as a house, education, a new tv (okay maybe not the tv). While this isn’t exactly a personal finance website, we can use spreadsheets to help visualize how much future money will be needed to repay the loan.
Today’s spreadsheet will walk through how to create a debt amortization spreadsheet. It will take a user’s inputs such as the amount borrowed, interest rate, term, and spit out an interest and payment table.
Spreadsheet Inputs
- The amount borrowed, which is also known as the “principal.” This is the total loan amount.
- The annual interest rate, which is about 3% for mortgages these days.
- The term, which is the number of months or years over which you are borrowing.
- Whether you want to calculate your payments monthly or annually.
Here’s what we have so far:
Spreadsheet Calculations and Output
The real magic of calculating debt payments is in the finance function PMT(). This function will take your principal, interest rate, and term, and calculate the level amortizing periodic payment. Here’s how we use it in our spreadsheet to calculate the annual payment for our example mortgage:
Next we make a schedule of each of the payment periods, where we keep a running tally of the beginning principal owed, the interest paid, the principal paid, and the ending principal owed.
Now we fill out the table. The first beginning principal comes from cell B5, the payment comes from cell B12, the interest paid is calculated by multiplying the beginning principal with the interest rate, the principal paid is the difference between the payment and interest paid, and the ending principal is the beginning principal minus the ending principal. The next period’s beginning principal is the previous period’s ending principal.
Was that clear?
That’s pretty much it. Feel free to walk through it yourself. I added in the logic to make it flexible for monthly or annual calculations.
Excel: Debt Amortization Spreadsheet
Google Sheets: Debt Amortization Spreadsheet (you can go to File –> Save a Copy to copy it to your Google Drive to edit it)