Analytical solution for annual mortgage interest?

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
SmallSaver
Posts: 255
Joined: Mon Mar 26, 2012 11:34 am

Analytical solution for annual mortgage interest?

Post by SmallSaver »

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.
Twins Fan
Posts: 2775
Joined: Fri Mar 08, 2013 1:02 pm

Re: Analytical solution for annual mortgage interest?

Post by Twins Fan »

Well, the equity at the time will depend on what the house is worth then. But, I shouldn't be that picky... :D

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.
bhuser01
Posts: 33
Joined: Mon Sep 27, 2010 1:48 pm

Re: Analytical solution for annual mortgage interest?

Post by bhuser01 »

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)
Topic Author
SmallSaver
Posts: 255
Joined: Mon Mar 26, 2012 11:34 am

Re: Analytical solution for annual mortgage interest?

Post by SmallSaver »

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.
sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Analytical solution for annual mortgage interest?

Post by sscritic »

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)
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
User avatar
G-Money
Posts: 2867
Joined: Sun Dec 09, 2007 7:12 am

Re: Analytical solution for annual mortgage interest?

Post by G-Money »

I like this one (be sure to check the box for "Show Amortization Table"): http://bretwhissel.net/cgi-bin/amortize
Don't assume I know what I'm talking about.
sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Analytical solution for annual mortgage interest?

Post by sscritic »

He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.
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?
P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.
User avatar
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?

Post by jimb_fromATL »

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:
  • 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 .)
    FV() -- Future Value-- to find the Future Value of an investment when you know the current balance, monthly payment, rate and time.
    • 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.)
    RATE() to find the rate required to earn a given amount when you know the beginning balance and/or monthly payment, and the time and goal.
    • 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.
Excel also has a lot of other financial functions that go into more complicated calcluations ... but most folks can get buy with the above basic building blocks for comparing mortgages, savings, and investments.

Google has a free online spreadsheet package, and I think Microsoft does too. Plus there are open-source 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.
To compare another loan, or the effect of rolling in closing costs
  • 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.
    You can then use simple math in other cells to calculate the differences for the numbers that show up in the cells that contain the function calls.
    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.
To find out how long it would take to pay off the present balance with the old payment:
  • =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 break-even 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)
    Notice that both formulas use the same cell for the breakEvenMonth.

    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.
Hope this helps.

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
Twins Fan
Posts: 2775
Joined: Fri Mar 08, 2013 1:02 pm

Re: Analytical solution for annual mortgage interest?

Post by Twins Fan »

sscritic wrote:He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.
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?
P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.
So, do you know of anything to help the OP out?

Or, does it just make you feel good to live up to your screen name and be a critic?
sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Analytical solution for annual mortgage interest?

Post by sscritic »

Twins Fan wrote:
sscritic wrote:He didn't ask for a calculator, he asked for a formula he can put in his own spreadsheet.
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?
P.S. Look at the thread title. "Analytical solution" does not mean a web page with a built in calculator.
So, do you know of anything to help the OP out?

Or, does it just make you feel good to live up to your screen name and be a critic?
I know reading is difficult, but what do you think this meant?
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
P.S. And where is your analytical solution? I must have missed it.
hexagon
Posts: 106
Joined: Fri Aug 24, 2007 8:32 am

Re: Analytical solution for annual mortgage interest?

Post by hexagon »

The formula is a straightforward application of the finite geometric sum formula:

1+r+...+r^(n-1)=(1-r^n)/(1-r) 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
sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Analytical solution for annual mortgage interest?

Post by sscritic »

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).
trying to get annual snapshots of mortgage interest paid and home equity.
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.
Last edited by sscritic on Thu Jan 02, 2014 1:11 pm, edited 1 time in total.
User avatar
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?

Post by jimb_fromATL »

hexagon wrote:The formula is a straightforward application of the finite geometric sum formula:

1+r+...+r^(n-1)=(1-r^n)/(1-r) 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
Yep. But I don't know why anybody who is already using a spreadsheet would ever need it.

As I showed in my previous post, the PMT() and FV() functions process the math formulas for you in one cell.

jimb
sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Analytical solution for annual mortgage interest?

Post by sscritic »

The terms in the link I provided earlier can also be found using the functions given by jimb_fromATL.
User avatar
LadyGeek
Site Admin
Posts: 71229
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Analytical solution for annual mortgage interest?

Post by LadyGeek »

jimb_fromATL wrote:Folks who are not familiar with Excel or other spreadsheets can do it with online calculators that build amortizations schedules...
Thanks to jimb_fromATL's post in: Re: Financial math question, the above post is now linked from the wiki.

See: Comparing investments (Comparing mortgages)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
ubermax
Posts: 1729
Joined: Tue Feb 11, 2014 2:19 pm
Location: Connecticut

Re: Analytical solution for annual mortgage interest?

Post by ubermax »

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 .
Post Reply