Can anyone assist in using Excel to write a formula for the annual
percentage rate for irregular transactions? The OCC has a program
named WINAPR that can calculate the annual percentage rate;
however, I need to know how to make this computation in Excel.
Disclaimer: The following is personal use only. If you are a
professional in the financial industry, you should not rely on the
following or any free advice for business purposes, except to the
extent that you assume all liability. "You get what you pay for".
Since you mention the OCC and WinAPR, I presume you are asking about
the APR calculation for US secured loans. And presumably WinAPR
follows the US "Truth in Lending" regulations, in particular Appendix
J of Reg Z.
I hope you understand that mortgage APR calculations are a black art.
Well, the math is canonical. But there is a great deal of latitude in
specifying up-front closing costs and variable rates. If you have
WinAPR available, you should compare solutions with WinAPR and Excel,
and fine-tune the latter to match the former.
With all that in mind, you can think of the APR as an IRR. So you can
use Excel's IRR (or XIRR) function for irregular payments at regular
intervals; or you can use XIRR for irregular payments at irregular
intervals.
If you use IRR, remember that the Excel function returns the periodic
rate. To annualize that IRR in accordance with Reg Z, you need to
compound the periodic rate, not simply multiply by the number of
periods per year. For example, if the IRR period is monthly, the APR
would be (1+IRR(...))^12 - 1, not simpy 12*IRR(...). In contrast,
Excel's XIRR function returns an annualized rate.
Consider the following example. A variable-rate 5-year loan of
$250,000 paid quarterly at 3% the first year and 6% the remaining
years.
For the first term, the payment is about $13,508 [=roundup(pmt(3%/
4,5*4,-250000),0)], with an ending balance of about $202,942 [=fv(3%/
4,4,13508,-250000)].
For the second term, the payment is about $14,362 [=round(pmt(6%/
4,4*4,-202942),0]. The last-1 ending balance is about $14,136 [=fv(6%/
4,4*4-1,14362,-202942)], so that last payment is about $14,348
[=roundup(14136*(1+6%/4),0)].
(Note: I actually substituted cell references for derived figures
like 202942 and 14136.)
So if A1=250000, A2:A5=-13508, A6:A20=-14136, and A21=-14348, the APR
would be (1+IRR(A1:A21))^4-1 (5.00%).
(Note: If you add dates and use XIRR, the result will probably be
slightly difference because XIRR uses the actual number of days
between dates, whereas IRR assumes an equal number of days.)
HTH. If that example is not apropos to your situation, please provide
an example of your own.
