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
- An explanation of the inner mathematical workings of excels rate function so I can analyze it myself
- An explanation of why my by hand APR calculation is wrong
- 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.
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)
APR = ([(Mortgage Balance at end of term + Interest Paid over term)/(Net Loan Advance)]-1)/3
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!