# Mortgage spreadsheet help

August 30, 2007 8:02 PM Subscribe

Looking for a mortgage calculator/spreadsheet (or help in creating a spreadsheet) that works for our situation.

We have a (Canadian) variable rate mortgage (the mortgage rate changes as the bank's prime rate changes). But instead of having a variable payment (as would normally be the case), the payment is a fixed amount. The fixed amount is what the payment would be for a mortgage with an interest rate of X%, where X is 2.5 more than the interest rate on our mortgage when we started. So we've been making extra payments with every (biweekly) payment for a few years. Now that the actual interest rate has caught up to our payment amount, I'd like to figure out what benefit we've received from this arrangement (and what changes we should make)

The two main problems I've got with mortgage spreadsheets /calculators I've found online so far:

(1) They don't allow for interest rates to change mid-month,

(2) They make you enter the "regular" payment and the "extra" payment separately -- the number I have is the total payment, and the computer is supposed to be doing the mathy bits for me

I'm pretty good with spreadsheets (I've made one for my taxes that has a cell for every box I need to fill in on the forms and does all the calculations) but mortgage formulas are a little black-magicky to me. Maybe I'm making this more complicated than it is?

We have a (Canadian) variable rate mortgage (the mortgage rate changes as the bank's prime rate changes). But instead of having a variable payment (as would normally be the case), the payment is a fixed amount. The fixed amount is what the payment would be for a mortgage with an interest rate of X%, where X is 2.5 more than the interest rate on our mortgage when we started. So we've been making extra payments with every (biweekly) payment for a few years. Now that the actual interest rate has caught up to our payment amount, I'd like to figure out what benefit we've received from this arrangement (and what changes we should make)

The two main problems I've got with mortgage spreadsheets /calculators I've found online so far:

(1) They don't allow for interest rates to change mid-month,

(2) They make you enter the "regular" payment and the "extra" payment separately -- the number I have is the total payment, and the computer is supposed to be doing the mathy bits for me

I'm pretty good with spreadsheets (I've made one for my taxes that has a cell for every box I need to fill in on the forms and does all the calculations) but mortgage formulas are a little black-magicky to me. Maybe I'm making this more complicated than it is?

Your situation seems pretty complicated so I don't know if this will help, but I usually recommend Karl's Mortgage Calculator.

posted by procrastination at 3:48 AM on August 31, 2007

posted by procrastination at 3:48 AM on August 31, 2007

This thread is closed to new comments.

Use the PMT function to figure out the implied payment, Use the FV function to figure out the implied amortization. The FV function will give you the remaining principle and from that amount subtract the extra you paid over the PMT figure. Then do it again for each successive time period.

posted by JPD at 8:45 PM on August 30, 2007