This spreadsheet (in Open Office format) calculates payoff period, remaining loan after 5, 7, and 10 years, total interest paid, etc. It includes a set of payoff functions in StarOffice Basic. It is usefull to calculate, among other things, whether you should pay mortgage points up front, or go with a higher mortgage rate.
I was thinking of a way to compare different loans with. I finally came up with this spreadsheet where these items should be entered: The first set is probably constant througout the whole spreadsheet.
These parameters you need to enter for each mortgage offer:
The spreadsheet will calculate how large loan you have to take. For example (see the screenshot), I want to buy property that costs $199,000, I have $55,000 available for downpayment and closing costs, and I know I can afford to pay $1,250 every month. Now for each mortgage offer, I enter rate, points, and fees. I decided that I might sell the property after 7 years, so I am looking for an offer that has the lowest "Remaining Loan" after that period (see cell AB29). The offer from the Valley National Bank was the best: 4.875% rate with 2.0 points. So the spreadsheet calculates that I should take the loan of $148,469 (cell AB7). I will put a downpayment of $50,531 (cell AB9), and the remainder to $55,000 will be used to pay bank fees and points.
The implementation of the payment function in StarOffice BASIC is extracted for your convenience in:
The notation is:
r is monthly rate factor(e.g. r = 100% + 4.875%/12 = 1.0040625)
p is monthly payment (e.g. $1,250 per month)
v0 is initial loan (e.g. $148,469)
v is remaining loan after some period (e.g. after 7yrs=84mo, v=$83,867).
When you get a loan, after the first month you incurr interest on the whole loan v0, and make one payment of p.
balance = v0*r - p
The next month you pay the interest on the balance:
balance_2= (v0 r - p)r - p balance_3= ((v0 r - p)r - p)r - p balance_4= (((v0 r - p)r - p)r - p)r - p
... and so on. After N payments, the balance is:
balance_n= v0*r^n + p*r^(n-1) + p*r^(n-2) + ... + p
= v0*r^n + p *(r^(n-1) + r^(n-2) + ... + 1)
balance_n= v0*r^n - p * (1-r^n)/(1-r)
And that is the final formula for the payment function (built in -PMT() function). When the balance goes to zero, n = number of payments needed to pay off the debt. Or solve the function for p = default payment needed in order to pay off the debt in n number of months.
balance_n = v0*r^n - p * (1-r^n)/(1-r) default_payment = (v0*r^n - v) * (1-r)/(1-r^n) payoff_period = Log( (v + p/(1-r)) / (v0 + p/(1-r)) ) / Log(r)
The StarOffice BASIC source code macro calculates these three functions. The functions inputs are the period years, and annual rate.