Tag: mortgage

How much is your low interest rate mortgage worth?

One of the ideas floating around lately is that if you were lucky enough to secure a low interest rate, it is actually a hidden asset in your net worth.

Of course, it’s a little more complicated than that, for example if you move/sell you have to repay your mortgage and that asset would disappear, which is not what typical assets actually do.

But let’s have some fun for a moment and pretend that by borrowing at a low rate we have effectively shorted a 30 year bond, and see how much value we have generated by this trade.

We’ll repurpose our debt amortization spreadsheet from an earlier post.

All we need to do now is to to add a few additional inputs – how many years are left on the mortgage, and what the current mortgage rate is. We’ll then calculate the net present value of the remaining mortgage payments for each year using the current mortgage rate as the discount rate. The difference between that NPV and your mortgage principal is the “asset value”. 

In this example, in year 5 on an original $500k mortgage at 3.25%, the “asset value” of keeping this mortgage when the current market mortgage rate is 7.9% is $170k!

You can play with the Google Spreadsheet here

Build a debt amortization spreadsheet

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)