Google Answers Logo
View Question
Q: How do I calculate IRR manually? ( Answered 5 out of 5 stars,   0 Comments )
Subject: How do I calculate IRR manually?
Category: Business and Money > Accounting
Asked by: robert67-ga
List Price: $20.00
Posted: 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
Subject: Re: How do I calculate IRR manually?
Answered By: omnivorous-ga on 31 May 2005 11:33 PDT
Rated:5 out of 5 stars
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)

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?

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,

robert67-ga rated this answer:5 out of 5 stars
Excellent response and very quick.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy