Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR formula for irregular transactions
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. Thanks, JR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
APR formula for irregular transactions
On Feb 3, 8:26 am, JR wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, Can I only have it bring up a transactions once? | Excel Discussion (Misc queries) | |||
Total each item's transactions | Excel Discussion (Misc queries) | |||
Log of Transactions | Excel Discussion (Misc queries) | |||
The goal is 5 per 100 transactions, how do I write formula for % | Excel Discussion (Misc queries) | |||
couting transactions for same time | Excel Worksheet Functions |