Net Present Value Question
- Thread starter heideeee
- Start date
Usually, I do not answer "homework" questions with turnkey solutions. I don't want to deny you the satisfaction of solving the problem yourself.
But since I don't have a dispositive solution in this case, there seems to be little harm in offering some ideas. Beware that they might be misdirections.
More importantly to me, please post the correct answer if and when you learn it. All too often, students learn the answer from other students or from the instructor, and they never tell us.
I agree with pecoflyer: it would behoove to provide a link to an Excel file, uploaded to a file-sharing website, that demonstrates your work so far. At the very least, you should provide the formulas, even if they might be wrong.
To paraphrase Spider-Man: "With great details come great answers".
To that end, refer to my attached Excel file for details.
-----
What exactly is your answer? And show us the formula that you used?
For Option 1, E31 is highlighted. Should E32 be highlighted as well for Option 2?
Or is that your attempt to answer Option 1?
Ostensibly, the calculation (?) in E25 shows the correct use of Excel NPV, presumably:
=NPV(D24, F22:H22) + E22
Note that the initial investment (E22), which occurs in Year 0, the "present value" time, is not included in the Excel NPV range list.
That is because Excel NPV discounts the first value in the range list.
That differs from most mathematical descriptions of the NPV calculation, namely:
In contrast, the Excel NPV calculation is:
Thus, I believe that the Option 1 NPV should be calculated in E31 as follows:
=NPV(D31, $F$22:$G$22, $H$22+H31) + $E$22
where D31 is the discount rate (7%), and H31 is the net sale amount ($2,000,000).
But that results in $832,392.78 (rounded), not $837,706.88, which you indicate is the expected answer.
Likewise, my Option 2 NPV calculation in E32 is:
=NPV(D32, $F$22:$G$22, $H$22+H32) + $E$22
where D32 is 4%, and H32 is $1,800,000.
That results in $811,508.59 (rounded), not $817,239.95. Again, is the latter value the expected answer?
How do my results compare with yours?
-----
There are a number of ambiguities in the assignment, as you presented it, that might explain the different NPV results.
The key issue is the row title "Expected Net Sale Amount, Inflation Adjusted". Huh?!
For Option 1, is $2,000,000 the actual sale amount in Year 3, as my NPV formulas assume?
Or is it the PV of the Year 3 amount, already discounted by the inflation rate?
In the latter case, the NPV formula for Option 1 might be:
=NPV(D31, $F$22:$H$22) + $E$22 + H31
But that results in $1,199,797.03 (rounded). Obviously too high.
Alternatively, if $2,000,000 is the actual sale amount in Year 3, should we discount it to Year 0 by the inflation rate (3%) instead of the cash flow discount rates for Option 1 (7%)?
In that case, the NPV formula for Option 1 might be:
=NPV(D31, $F$22:$H$22) + $E$22 + H31/(1+D12)^(1/3)
(The inflation rate seems to be 3%, based on the value in D12 for the "Base Rent Adjustment for Inflation".)
That results in $1,180,187.92 (rounded). Again, too high.
-----
I have tried some other analytic approaches to reverse-engineer green-highlighted NPV for Option 1 in E31.
Since none has panned out, I won't bother you with the details.
-----
But since I don't have a dispositive solution in this case, there seems to be little harm in offering some ideas. Beware that they might be misdirections.
More importantly to me, please post the correct answer if and when you learn it. All too often, students learn the answer from other students or from the instructor, and they never tell us.
I agree with pecoflyer: it would behoove to provide a link to an Excel file, uploaded to a file-sharing website, that demonstrates your work so far. At the very least, you should provide the formulas, even if they might be wrong.
To paraphrase Spider-Man: "With great details come great answers".
To that end, refer to my attached Excel file for details.
-----
What exactly is your answer? And show us the formula that you used?
For Option 1, E31 is highlighted. Should E32 be highlighted as well for Option 2?
Or is that your attempt to answer Option 1?
Ostensibly, the calculation (?) in E25 shows the correct use of Excel NPV, presumably:
=NPV(D24, F22:H22) + E22
Note that the initial investment (E22), which occurs in Year 0, the "present value" time, is not included in the Excel NPV range list.
That is because Excel NPV discounts the first value in the range list.
That differs from most mathematical descriptions of the NPV calculation, namely:
In contrast, the Excel NPV calculation is:
Thus, I believe that the Option 1 NPV should be calculated in E31 as follows:
=NPV(D31, $F$22:$G$22, $H$22+H31) + $E$22
where D31 is the discount rate (7%), and H31 is the net sale amount ($2,000,000).
But that results in $832,392.78 (rounded), not $837,706.88, which you indicate is the expected answer.
Likewise, my Option 2 NPV calculation in E32 is:
=NPV(D32, $F$22:$G$22, $H$22+H32) + $E$22
where D32 is 4%, and H32 is $1,800,000.
That results in $811,508.59 (rounded), not $817,239.95. Again, is the latter value the expected answer?
How do my results compare with yours?
-----
There are a number of ambiguities in the assignment, as you presented it, that might explain the different NPV results.
The key issue is the row title "Expected Net Sale Amount, Inflation Adjusted". Huh?!
For Option 1, is $2,000,000 the actual sale amount in Year 3, as my NPV formulas assume?
Or is it the PV of the Year 3 amount, already discounted by the inflation rate?
In the latter case, the NPV formula for Option 1 might be:
=NPV(D31, $F$22:$H$22) + $E$22 + H31
But that results in $1,199,797.03 (rounded). Obviously too high.
Alternatively, if $2,000,000 is the actual sale amount in Year 3, should we discount it to Year 0 by the inflation rate (3%) instead of the cash flow discount rates for Option 1 (7%)?
In that case, the NPV formula for Option 1 might be:
=NPV(D31, $F$22:$H$22) + $E$22 + H31/(1+D12)^(1/3)
(The inflation rate seems to be 3%, based on the value in D12 for the "Base Rent Adjustment for Inflation".)
That results in $1,180,187.92 (rounded). Again, too high.
-----
I have tried some other analytic approaches to reverse-engineer green-highlighted NPV for Option 1 in E31.
Since none has panned out, I won't bother you with the details.
-----
As noted previously, those formulas do not return the expected results.
However, I do get the expected results (rounded) with those formulas when:
1. The Option 1 net sale amount is $2,006,510 instead of $2,000,000; and
2. The Option 2 net sale amount is $1,806,447 instead of $1,800,000.
Does the assignment provide any additional information that would explain those larger net sale amounts?
Note that they are 0.33% to 0.36% more than the gross sale amounts.
-----