How do I calculate IRR manually?
Category: Business and Money > Accounting
Asked by: robert67-ga
List Price: $20.00
31 May 2005 06:27 PDT
Expires: 30 Jun 2005 06:27 PDT
Question ID: 527673
I am converting a spreadsheet, that uses the IRR function in Excel to calculate the % APR of a loan, to an interactive website. Unfortunately the language I am using to produce the website, PHP, unsurprisingly does not have an IRR function. The first part of my question is; Is my client calculating the APR correctly in the spreadsheet? The second part is; how do I manually calculate the IRR using standard mathematical functions in order to replicate the spreadsheet. Here is the information from the spreadsheet; A Bridging loan of 150k is taken out for 6 months at a monthly interest rate of 1.25%. Initial loan after fee's : 147,420 Payment 1 : -1,875.00 Payment 2 : -1,875.00 Payment 3 : -1,875.00 Payment 4 : -1,875.00 Payment 5 : -1,875.00 Payment 6 : -1,875.00 Final payment : 150,000.00 IRR calculated using IRR(Initial loan:Final payment,1.25%) = 1.338% APR calculated using (1+IRR result)^12-1 = 17.28% Any help appreciated, and I can provide more info if required
Re: How do I calculate IRR manually?
Answered By: omnivorous-ga on 31 May 2005 11:33 PDT
Robert67 ? In his finance presentation below Prof. Ramana Sonti notes ?"An easy way of thinking about IRR is to define it as the rate that forces the NPV of the project to zero.? Kent Sate University "Intermediate Financial Management," (Ramana Sonti) http://business.kent.edu/courses/spring02/Fin/36054/lecture_notes/chapter6.htm But that little bit of ?simplicity? hides how hard it can be to calculate IRR. Indeed, the notes for Microsoft Excel?s IRR function say that the program ?uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.? --- Your calculations in this problem are essentially correct ? particularly the APR. However, there are some assumptions that must be made, in particular when the final payment is made. I?ve assumed in the linked spreadsheet below that the $150,000 principal is repaid at the end of month 6, just as the loan payments would normally be made at the end of the month: ?IRR Manual Calculation? http://www.mooneyevents.com/irrmanual.xls I?ve set up the spreadsheet so that you can change cell #B8 and arrive at a monthly IRR and also the APR. However, I?ve used 1.5% per month just to get things going and you can see that the NPV is still negative, so the number isn?t high enough. If you?ll be programming this IRR calculation, here are the things to consider: ? it?s rare that you?ll get an NPV of exactly zero. So choose the number of decimal places that you want (I?ve used 5 digits for the monthly number). ? You?ll then want to program your applet to choose the smallest POSITIVE number for monthly IRR My calculations, using iterations from the linked spreadsheet above to 5 decimal places show that 0.01553 or 1.553% PER MONTH is your number and that the annualized rate (largely due to the large discount paid upfront) is 20.3131% If any part of this is unclear, don?t hesitate to ask for a clarification before rating the answer. Google search strategy: IRR + NPV + calculation Best regards, Omnivorous-GA
rated this answer:
Excellent response and very quick.
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|