IRR desired return

[izbix]

I am doing a business plan and need baby step help on how to set up the
calculation of an IRR so that I can dp several 'what ifs' and also be able to
explain to the client how the spread sheet works. I need to be walked
through step by step as i can't seem to get the 'HELP ' to be clear enough
for me to get the right answer instead of error messages
Assume $3,000,000 investment
15 year amortization
desired IRR of 10% -- what would the monthly payments need to be to get this

I iam using Excel 2000

 

[JE McGimpsey]

If you expect payments to be constant, one way:

=PMT(10%/12, 15*12,-3000000)

IRR is usually used for variable cash flows.

 

[joeu2004]

If you expect payments to be constant, one way:
=PMT(10%/12, 15*12,-3000000)

That is a very common conception of the monthly IRR -- the annual rate
divided by 12. Many academicians will agree.

But I (and many academicians) believe the better answer is:

=pmt(rate(12,0,-1,1+10%), 15*12, -3000000)

In other words, the monthly IRR should be a rate that compounds to 10%
per year, the required annual IRR stated in the problem.

There are many ways to explain why; I've tried many times. Perhaps
the easiest way is by demonstration.

If you agree that the annualized IRR() and XIRR() result should be the
same, given the same conditions, then set up a table for XIRR() for
this problem. For example, use 1/1/2007 for the initial cash flow of
-3,000,000 and 2/1/2007 through 1/1/2022 for the remaining cash flows,
which are equal to the result of the PMT() function.

Notice that XIRR() returns about 10.00% with 2nd formula, but about
10.47% with the 1st formula.

(Of course, the XIRR() result is slightly different because the number
of days between the same day of consecutive months is not exactly
equal, whereas IRR [and PMT] presume they are. For this example, the
difference is less than 0.0043 percent points.)

My advice to students: ask your teacher which way he/she prefers to
annualize IRR. As I said many times before, academicians seem to be
split 50-50.

Endnotes:

1. The RATE() function above can be replaced by (1+10%)^(1/2) - 1.

--- original posting ----

 

[joeu2004]

Errata....

Endnotes:
1. The RATE() function above can be replaced by (1+10%)^(1/2) - 1.

Typo... Of course that should be 1/12.