I would like rate of return by using the time value of money formula which is A= P(1+r)^n
That is the time value of money formula only in the special case where there is one investment (P) and one ending value (A) "n" years later.
In fact,
the IRR is the time-valued rate of return of the series of investments and withdrawals. That is, it is the average compounded rate of return. To demonstrate:
| Book1.xlsx |
|---|
|
|---|
| A | B | C | D |
|---|
| 1 | period# | cash flow | ending
balance | IRR |
|---|
| 2 | 0 | -100,000.00 | 100,000.00 | 13.84% |
|---|
| 3 | 1 | -100,000.00 | 213,841.68 | |
|---|
| 4 | 2 | -100,000.00 | 343,440.97 | |
|---|
| 5 | 3 | -100,000.00 | 490,978.99 | |
|---|
| 6 | 4 | -100,000.00 | 658,938.75 | |
|---|
| 7 | 5 | -100,000.00 | 850,146.97 | |
|---|
| 8 | 6 | -100,000.00 | 1,067,821.63 | |
|---|
| 9 | 7 | 160,000.00 | 1,055,626.13 | |
|---|
| 10 | 8 | 160,000.00 | 1,041,742.56 | |
|---|
| 11 | 9 | 160,000.00 | 1,025,937.28 | |
|---|
| 12 | 10 | 160,000.00 | 1,007,944.28 | |
|---|
| 13 | 11 | 160,000.00 | 987,460.74 | |
|---|
| 14 | 12 | 160,000.00 | 964,141.94 | |
|---|
| 15 | 13 | 160,000.00 | 937,595.42 | |
|---|
| 16 | 14 | 160,000.00 | 907,374.41 | |
|---|
| 17 | 15 | 160,000.00 | 872,970.31 | |
|---|
| 18 | 16 | 160,000.00 | 833,804.11 | |
|---|
| 19 | 17 | 160,000.00 | 789,216.64 | |
|---|
| 20 | 18 | 160,000.00 | 738,457.52 | |
|---|
| 21 | 19 | 160,000.00 | 680,672.47 | |
|---|
| 22 | 20 | 160,000.00 | 614,889.01 | |
|---|
| 23 | 21 | 700,000.00 | 0.00 | |
|---|
|
|---|
Code:
Formulas:
C2: =-B2
C3: =C2*(1+$D$2)-B3
D2: =IRR(B2:B23)
Copy C3 into C4:C23
The value in D2 (IRR) is more precisely 13.8416841708466%.
-----
The annual rate of return that scottleger4 calculated is an
arithmetic average simple return. More generally, it is:
=(SUMIF(B2:B23,">0") / (-SUMIF(B2:B23,"<0")) - 1) / (COUNT(B2:B23) - 1)
That returns 15.2380952380952%.
Perhaps by "rate of return by using the time value of money", you want the
compounded (aka geometric) average simple return. That is:
=(SUMIF(B2:B23,">0") / (-SUMIF(B2:B23,"<0"))) ^ (1 / (ROWS(B2:B23) - 1) - 1
That returns 7.07264660663853%.
Alternatively, I wonder if you want the MIRR ("modified IRR"). The name is a misnomer: it has nothing to do with the IRR. In particular, it is calculated directly, not estimated by an iterative process.
Like scottleger4's formula, the MIRR essentially calculates a
compounded periodic return (annual, in your case) of the sum of withdrawals divided by the sum of investments.
But we use the FV of all withdrawals, as if they occur in year 23, appreciated by the (re)investment rate. And we use the PV of all investments, as if they occur in year 0, discounted by the "finance" rate. See the MIRR help page for the mathematical formula.
In your case, you would use 7.75% for both rates, to wit:
=MIRR(B2:B23, 7.75%, 7.75%)
That returns 10.7186064155556%.
It should be noted that none of these rates can be substituted for the IRR in D2 in the table above. In other words, none is really a time-valued rate of return.
the discounting factor which would be 7.75 percent. How can I discount inflows which I receive from 8th year onwards in NPV formula? Also outflows are made in successive years. How to incorporate that in NPV?
It is unclear what you mean. The straight-forward NPV calculation of all of the cash flows is:
=NPV(7.75%,
B3:B23)
+ B2
or
=NPV(7.75%,
B2:B23)
*(1+7.75%)
The adjustments are necessary because the
Excel NPV function discounts the first cash flow in the series, effectively calculating the PVs to one period
before the first cash flow.
(MSFT did that for compatibility with Visicalc and Lotus 1-2-3, which were the industry leaders at the time.)
That is uncommon. Usually, the PVs are discounted to
the period of the first cash flow.
But to answer your question, for what it's worth (unclear), the NPV of the cash flows for periods 8 through 21 can be discounted to period 0 with the following formula:
=NPV(7.75%, B9:B23) / (1+7.75%)^ROWS(B2:B8)
-----
It might be helpful to know why you are asking these questions.
If they are for a class assignment, it would be helpful if you provided a link to or an image of the actual assignment.
Alternatively, if you are interested in the
market return of such a series of cash flows, you might be interested in the so-called "time-weighted" rate of return (TWR or TWRR) -- another misnomer. That is an international industry standard. But the TWR calculation requires more information.
