Analytical solution for annual mortgage interest?

 Posts: 255
 Joined: Mon Mar 26, 2012 11:34 am
Analytical solution for annual mortgage interest?
I'm playing around with a spreadsheet to look at some home buying scenarios, and am trying to get annual snapshots of mortgage interest paid and home equity. It's easy enough to do this monthly and add them up for annual, but I wonder if anybody knows of a way to do the calculation directly? To put it another way, iI'd like to be able to say " in year X you will have paid Y in interest and have Z equity in the house", without calculating all of the months in between. I'm a little mathy, but not enough to work it out myself.
Re: Analytical solution for annual mortgage interest?
Well, the equity at the time will depend on what the house is worth then. But, I shouldn't be that picky...
Here is a site with calculators I love messing around with...
http://www.mtgprofessor.com/CalculatorA ... ators.html
I'm sure one of the calculators will tell you what you want.
Here is a site with calculators I love messing around with...
http://www.mtgprofessor.com/CalculatorA ... ators.html
I'm sure one of the calculators will tell you what you want.
Re: Analytical solution for annual mortgage interest?
http://www.hughchou.org/calc/formula_deriv.php
http://www.vertex42.com/Calculators/hom ... lator.html (This has an excel sheet with an amortization table, which is useful. Be careful about the Download button; the first one is an advertisement)
http://www.vertex42.com/Calculators/hom ... lator.html (This has an excel sheet with an amortization table, which is useful. Be careful about the Download button; the first one is an advertisement)

 Posts: 255
 Joined: Mon Mar 26, 2012 11:34 am
Re: Analytical solution for annual mortgage interest?
Thanks all, the sum of a series may be what I'm after, I'll have to think about that for a minute. The spreadsheet calculator looks good too, I've been rolling my own but can maybe use some bits.
Re: Analytical solution for annual mortgage interest?
The first link contains a sign error.bhuser01 wrote:http://www.hughchou.org/calc/formula_deriv.php
http://www.vertex42.com/Calculators/hom ... lator.html (This has an excel sheet with an amortization table, which is useful. Be careful about the Download button; the first one is an advertisement)
See this page, which shows that you can break your current balance into two parts: the debt assuming you never make a payment and the value of the series of payments you have made. What you owe at any point is the difference.
http://www.financeformulas.net/Remainin ... rmula.html
Re: Analytical solution for annual mortgage interest?
I like this one (be sure to check the box for "Show Amortization Table"): http://bretwhissel.net/cgibin/amortize
Don't assume I know what I'm talking about.
Re: Analytical solution for annual mortgage interest?
He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.
P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.I'm playing around with a spreadsheet to look at some home buying scenarios, and am trying to get annual snapshots of mortgage interest paid and home equity. It's easy enough to do this monthly and add them up for annual, but I wonder if anybody knows of a way to do the calculation directly?
 jimb_fromATL
 Posts: 2278
 Joined: Sun Nov 10, 2013 12:00 pm
 Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.
Re: Analytical solution for annual mortgage interest?
Folks who are not familiar with Excel or other spreadsheets can do it with online calculators that build amortizations schedules. Many of the online calculators will also tell you the time required if you specify the payment. You can generate the amortization schedules for two mortgages, then look at the balances at any step in the schedule.
You don't have to use the actual math formulas. Excel and other spreadsheets and software packages have standard software library functions that do the sometimes complicated and tedious repetitive math formulas and iterative calculations for compound interest for you and return the result in a single cell.
The borrower's debt is the lender's investment with interest compounded monthly for mortgages and most other loans.. For the lender, a mortgage or other loan is an investment of a lump sum in an annuity that will pay back a specific monthly payment for a given balance, rate, and time, with interest compounded monthly. Therefore, the math formulas for totals, payments, etc for mortgages and other loans are the same as for the corresponding investments. The only difference is how you treat the numbers as postive or negative cash flow depending on whether you're the lender or the investor.
Here are some notes gathered from a lot of my old posts on the subject ... from other forum sites This should give you some ideas of what to research to learn how to do it yourself. Excel has a lot of help in the package and online that discuss the subject of annuities.
The math functions most often needed are:
Google has a free online spreadsheet package, and I think Microsoft does too. Plus there are opensource spreadsheets that you can download for free. However, very few if any of the freebies provide nearly as many features such as charting, graphing, problem solving, goal seeking, etc, or as much online help for programming as Excel does.
 examples 
First, solving for the payment:
In Excel you can name individual cells to make the easier to reference. For instance, you could put the rate in cell A3 and name cell A3 “newRate” and use the actual name instead of “A3” … and do the same for the other factors.
Finding the time remaining when you know the current balance, rate and payment.
Comparing two mortgages or the breakeven point for refinancing :
By the way, I write Excel spreadsheets to do the calculations and use the Excel text functions to generate the narratives that describe the calculations. Once I've written a spreadsheet, it's easy to change the parameters. I only have to copy the text narrative into a new paragraph or post for comparisons. I don't really have to type all those tedious numbers and all those words into my posts.
jimb
You don't have to use the actual math formulas. Excel and other spreadsheets and software packages have standard software library functions that do the sometimes complicated and tedious repetitive math formulas and iterative calculations for compound interest for you and return the result in a single cell.
The borrower's debt is the lender's investment with interest compounded monthly for mortgages and most other loans.. For the lender, a mortgage or other loan is an investment of a lump sum in an annuity that will pay back a specific monthly payment for a given balance, rate, and time, with interest compounded monthly. Therefore, the math formulas for totals, payments, etc for mortgages and other loans are the same as for the corresponding investments. The only difference is how you treat the numbers as postive or negative cash flow depending on whether you're the lender or the investor.
Here are some notes gathered from a lot of my old posts on the subject ... from other forum sites This should give you some ideas of what to research to learn how to do it yourself. Excel has a lot of help in the package and online that discuss the subject of annuities.
The math functions most often needed are:
 PMT() to find the monthly payment when you know the loan balance, rate, and time.
 (Or use it to to find out how much you need to invest to reach a goal when you know the rate and time and goal, or to find how much you can withdraw per month from an existing lump sum investment .)
 You can use FV to find the balance of a mortgage at any given month when you know the balance, rate and payment, instead of having to buil the entire monthly amortization table and looking it up.
NPER() number of periods required to pay off a loan when you know the Balance, Rate, and Payment. (Or to find how long it takes for an investment to grow to a given amount.)
 This also finds the rate of a mortgage when you know the balance, payment and time;
... or the annual rate of return when you know how much you invested for how long, and how much you have at the end of given time period;
...or the APR of a loan with closing costs included.
PV()  PresentValue  for finance, to show the present value of a future lump sum and/or series of equal payments.
Google has a free online spreadsheet package, and I think Microsoft does too. Plus there are opensource spreadsheets that you can download for free. However, very few if any of the freebies provide nearly as many features such as charting, graphing, problem solving, goal seeking, etc, or as much online help for programming as Excel does.
 examples 
First, solving for the payment:
 Suppose you know the balance, rate and time for a loan. You can use the PMT function in a cell to find out the monthly payment.
For example, if you put the annual Rate in one cell, the number of months for the loan in another, and the starting Balance in another, then the pmt formula might look like this, but using the cell addresses for the factors:
 =PMT(newRate/12,newmonths,newBalance)
In Excel you can name individual cells to make the easier to reference. For instance, you could put the rate in cell A3 and name cell A3 “newRate” and use the actual name instead of “A3” … and do the same for the other factors.
Finding the time remaining when you know the current balance, rate and payment.
 You could put those factors in cells for oldBalance, oldRate, and oldPayment. Then using the addresses of the factors, find out how long you'll owe on it with the NPER function:
=NPER(oldRate/12,oldPayment,oldBalance)
… gives the remaining months.
Multiply that by the payment to get the total cost in another cell.
 You might add the closing costs to get your new balance, and put your new rate and new mortgage length in cells, and solve for the new payment.
 =PMT(newRate/12,newmonths,newBalance)
... gives the new payment. Multiply it by the number of payments to get the total cost of the new mortgage.
Once you have a row of factors and the calculation for one loan, it's easy to copy and paste the data and formulas to other rows to build a comparison table.  =PMT(newRate/12,newmonths,newBalance)
 =NPER(newrate/12,oldPayment,newbalance)
… give the new number of months. Multiply it by the newPayment to get the total cost.
Comparing two mortgages or the breakeven point for refinancing :
 IMO the true break even point is when for exactly the same money out of pocket up front and in monthly payments, you will owe no more on the new mortgage than you would have on the old one. That's when your net worth has recovered from the extra closing costs.
To find the break even point, I create a new cell for the break even month.
Then create two new functions using the Future Value function to find out how much you'd owe after a given number of months for a known rate, payment, and current balance.
 =FV( oldRate/12, breakEvenMonth, oldPayment, oldBalance)
=FV(newRate/12, breakEvenMonth, newPayment,newBalance)
In another cell, I subtract the oldBalance from the newBalance. Then I use goalseek to make that difference between them equal to 0 by changing the value of the cell containing the breakEvenMonth.
In spreadsheets that don't support fancy stuff like goalseek, you can enter different numbers in the cell for the breakEvenMonth and narrow it down. That’s what goalseek does for you anyway.
If you enter a too large number, then a too small number, and cut the difference by half with each new entry, you can get very close within about 8 to 10 tries. Divide and conquer.  =FV( oldRate/12, breakEvenMonth, oldPayment, oldBalance)
By the way, I write Excel spreadsheets to do the calculations and use the Excel text functions to generate the narratives that describe the calculations. Once I've written a spreadsheet, it's easy to change the parameters. I only have to copy the text narrative into a new paragraph or post for comparisons. I don't really have to type all those tedious numbers and all those words into my posts.
jimb
Re: Analytical solution for annual mortgage interest?
So, do you know of anything to help the OP out?sscritic wrote:He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.I'm playing around with a spreadsheet to look at some home buying scenarios, and am trying to get annual snapshots of mortgage interest paid and home equity. It's easy enough to do this monthly and add them up for annual, but I wonder if anybody knows of a way to do the calculation directly?
Or, does it just make you feel good to live up to your screen name and be a critic?
Re: Analytical solution for annual mortgage interest?
I know reading is difficult, but what do you think this meant?Twins Fan wrote:So, do you know of anything to help the OP out?sscritic wrote:He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.I'm playing around with a spreadsheet to look at some home buying scenarios, and am trying to get annual snapshots of mortgage interest paid and home equity. It's easy enough to do this monthly and add them up for annual, but I wonder if anybody knows of a way to do the calculation directly?
Or, does it just make you feel good to live up to your screen name and be a critic?
P.S. And where is your analytical solution? I must have missed it.The first link contains a sign error.
See this page, which shows that you can break your current balance into two parts: the debt assuming you never make a payment and the value of the series of payments you have made. What you owe at any point is the difference.
http://www.financeformulas.net/Remainin ... rmula.html
Re: Analytical solution for annual mortgage interest?
The formula is a straightforward application of the finite geometric sum formula:
1+r+...+r^(n1)=(1r^n)/(1r) if r is not 1 and equals n if r is 1.
Wikipedia has a derivation of the formula if you don't want to work it out for yourself.
http://en.wikipedia.org/wiki/Amortizati ... he_formula
Doug
1+r+...+r^(n1)=(1r^n)/(1r) if r is not 1 and equals n if r is 1.
Wikipedia has a derivation of the formula if you don't want to work it out for yourself.
http://en.wikipedia.org/wiki/Amortizati ... he_formula
Doug
Re: Analytical solution for annual mortgage interest?
Not to put too fine a point on it, but I don't read that the OP wants to know the monthly payment. That he can get from a calculator [edit: or using the functions given by jimb_fromATL], what he wants, again, as I read, is mortgage interest paid and balance (or the equivalent, the amount already paid off).
To get the remaining balance, use the formulas in the link I provided earlier (yes, you have to click). Equity is original loan minus remaining balance. Interest paid is total payments (months times monthly payments) minus equity.trying to get annual snapshots of mortgage interest paid and home equity.
Last edited by sscritic on Thu Jan 02, 2014 1:11 pm, edited 1 time in total.
 jimb_fromATL
 Posts: 2278
 Joined: Sun Nov 10, 2013 12:00 pm
 Location: Atlanta area & Piedmont Triad NC and Interstate 85 in between.
Re: Analytical solution for annual mortgage interest?
Yep. But I don't know why anybody who is already using a spreadsheet would ever need it.hexagon wrote:The formula is a straightforward application of the finite geometric sum formula:
1+r+...+r^(n1)=(1r^n)/(1r) if r is not 1 and equals n if r is 1.
Wikipedia has a derivation of the formula if you don't want to work it out for yourself.
http://en.wikipedia.org/wiki/Amortizati ... he_formula
Doug
As I showed in my previous post, the PMT() and FV() functions process the math formulas for you in one cell.
jimb
Re: Analytical solution for annual mortgage interest?
The terms in the link I provided earlier can also be found using the functions given by jimb_fromATL.
Re: Analytical solution for annual mortgage interest?
Thanks to jimb_fromATL's post in: Re: Financial math question, the above post is now linked from the wiki.jimb_fromATL wrote:Folks who are not familiar with Excel or other spreadsheets can do it with online calculators that build amortizations schedules...
See: Comparing investments (Comparing mortgages)
Re: Analytical solution for annual mortgage interest?
Here are some formulas for a monthly payment 30 year mortgage which you can easily adapt for a different frequency and/or term :
let L = the loan amount ; i = the nominal annual rate ; j= the effective monthly rate = i/12 ; P = the monthly payment ; DP= down payment ; A = appreciation ( hopefully >0);
and let 1<=t<= 30, the successive yearly time points .
L(1+j)^^(12t)  P { (1+j)^^(12t) 1 } /j = Outstanding Loan Balance at Time t
L  (Outstanding Loan Balance at Time t ) = Partial Equity
12Pt  (Partial Equity ) = Total Interest Paid to Time t
Total Equity = Partial Equity + DP + A
Hope this is what you had in mind ; it assumes the closing date is a month before the first payment is due ; slight tweaking necessary if loan received < or > a month b4 the first due date but it'll generally be very close .
let L = the loan amount ; i = the nominal annual rate ; j= the effective monthly rate = i/12 ; P = the monthly payment ; DP= down payment ; A = appreciation ( hopefully >0);
and let 1<=t<= 30, the successive yearly time points .
L(1+j)^^(12t)  P { (1+j)^^(12t) 1 } /j = Outstanding Loan Balance at Time t
L  (Outstanding Loan Balance at Time t ) = Partial Equity
12Pt  (Partial Equity ) = Total Interest Paid to Time t
Total Equity = Partial Equity + DP + A
Hope this is what you had in mind ; it assumes the closing date is a month before the first payment is due ; slight tweaking necessary if loan received < or > a month b4 the first due date but it'll generally be very close .