|
Question 971795: How do I solve for X in excel?
Consider the following project’s cash flows:
n Net Cash Flow
0 -$15,500
1 $2,500
2 X
3 $7,500
Assume that the project’s IRR is 12%.
a) Find the value of X.
Answer by Theo(13342)   (Show Source):
You can put this solution on YOUR website! excel is tricky, but it can be done if you're careful.
there are two functions involved.
there's the NPV function and the IRR function.
the NPV function format is NPV(rate,data range).
the IRR function format is IRR(data range)
you set up your data in a range of cells with each time point being right under the other.
you put a zero in for the value of x that is missing.
you can't leave that field blank.
you have to put in a zero.
otherwise you'll get the wrong answer.
i know because i left it blank and had to do some extra analysis to understand why i wasn't getting what i expected to get.
you run the NPV function and it will tell you what the NPV is.
in this case, at a rate of .12, the NPV was ($7,079.92) which means a negative 7,079.92.
the value that was missing caused the NPV to be negative 7079.92.
those results are in the original column.
take that number and inflate it at the interest rate for the time period that it belongs in.
here's where excel gets tricky again.
it does a present value to one year more than the initial investment year.
so the value showed as in time point 2 but you have to inflate it 3 years and not two.
this conforms with the present value routine of excel used in the NPV function.
you also have to make it positive since it is a positive cash flow you are looking for.
7079.92 * 1.12^3 = 9946.777846
that's the value you need to replace the 0 from time point 2 with.
your revised cash flow is shown in the revised column.
the NPV becomes 0 as it should if the internal rate of return is 12%.
the irr field shows you the result of the IRR function of excel.
after the revision, you get an IRR of 12% as you should.
the results from my excel spreadsheet are shown below:
tp means time point
cf means cash flow
normally you present worth to time point 0.
excel went one time point further with their npv function.
it doesn't change the result.
it just give you a different number than you would expect if you knew that present worthing normally goes to time point 0.
|
|
|
| |
