Inner mathematical workings of excels rate function or why is the manual APR calculation different

finance

I am trying to understand how the APR calculations we are using at work, but calculating it by hand is giving me different answers. The answer I am looking for to this question is either

  1. An explanation of the inner mathematical workings of excels rate function so I can analyze it myself
  2. An explanation of why my by hand APR calculation is wrong
  3. An explanation of why our use of excels rate function is wrong.

Also sorry in advance for the formatting. I tried to fix it so it wasn't essentially one paragraph but I couldn't get it to do smaller or bigger line breaks.

I am asking here because my question is ultimately about the math and I am not convinced excel forums will care about the mathematical details. However, if anyone has a suggestion for a more appropriate forum, that would also be appreciated.

Here is an example of the math I am doing in excel with made up numbers.

The mortgage is an interest only mortgage, compounded monthly, where the interest is paid off every month.

Principle: $100,000

Fees (deducted from principal): $5,000

Net Loan Advance = Fees – Principle = $95,000
Interest Rate: 5.00%

Term: 36 Months Monthly payment: Principal* Interest/12 = $416.67

Mortgage Balance at end of Term= Principal = $100,000

Interest Paid over term = Loan Amount + Monthly payment* 36=$15,000

The APR function in place is excel's rate function:

rate(term, -monthly payment, net loan advance, -mortgage balance at end of term)*12

= rate(36, -416.67, $95,000, -$100,000)*12=6.85%

The calculation I am doing by hand is

Principal + Interest = Net Advance (3* APR +1)

Which gives

APR = ([(Mortgage Balance at end of term + Interest Paid over term)/(Net Loan Advance)]-1)/3

=([($100,000+$15,000)/($95,000)-1])/3=7.02%

This is equivalent to the APR formula I get by googling, for example here: https://www.lexingtonlaw.com/credit/what-is-apr

I was trying to figure out how the APR function works myself so I could analyse the differences, but apparently its complicated and calculated my making guesses in the other financial formulas, possibly the pmt function.

I tried looking up the details of the pmt function, but all could find was this https://superuser.com/questions/871404/what-would-be-the-the-mathematical-equivalent-of-this-excel-formula-pmt
which gives an answer when there is no future value.

In conclusion, if anyone knows what I am doing wrong, or the inner workings of these excel functions, it would be much appreciated!

Best Answer

In case anyone stumbles upon this years later, I figured it out.

The calculations I did without using excel's rate function were based on the assumption that, since the borrowers are paying off their interest every month, compounding does not matter.

This assumption is false

APR assumes that the principal is the amount actually netted to the borrower.

So in APR land, the borrower starts by owing 95,000 and ends up owing 100,000. Therefore it is as if they were not paying off all the interest every month.

Therefore compounding becomes an issue, and the simple formula:

Principal + Interest = Net Advance (3* APR +1)

Does not apply

Related Question