Net Present Value Calculation (2002)

Topic

New Reply

Hey, I am doing a NPV calculation of the following:
1. Sale of Facility in 2005 at $20M
2. Leaseback of Facility in 2006 and 2007 at $2M.

If I use a discount rate of 10%, does Excel’s NPV calcuation consider that I will earn 10% per year on the $20M or do I need to factor this into the calculation beginning in 2006?

Thanks.

Reply | Quote

Replies

I am no financial expert so I won’t delve into the calculations,, but since no one has responded yet, I will offer a suggestion that I frequently use::

Get several scenarios together (various “real-world items”, that cover the range of your situations).
Calculate the NPV the way you think it should be done for each of them.
Have excel calculate the NPV with its formula.

If the numbers are the same or “reasonably close” (I leave it to your “reason” to decide) you can be confident that the formula is calculated correctly for this situation. If none of them are correct, you need to find a different way in excel. If some are correct and others are wrong, one of the methods uses different logic and both do not always yield the same result and this should be investigated to see what is different (or if you made a math-error in your calcs).

[As a side note: this technique is a general procedure, it can be used for many applications, not just NPV and not just excel. I would never dream of using, eg, a statistical software package without doing several examples “hand-calculated” to make sure I understood where all the “computer numbers” come from.

I have used the technique frequently when I help my daughter with her homework. We have seen for a number of years, that I do not generally solve a problem the way she or her teacher ahs shown them how to do it. This lead to confusion when I intially tried to help. She now works out the problems her way and I work them out my way. If they match, we assume we got the correct answer. If they do not, I look to see if the logic is “similar” and if so, we check each others math to find out who made the math error (“Dad, this should be a negative number”). If we can find no math error or the logic is completely different, we delve into how they were solved to see where the logic fails…]

The key point is that unless you understand how to get the number and what the true number should be, it is tough to judge if excel is doing it correctly…

Just my
Steve

Reply | Quote

Yes but not explicity (if you know what I mean). The way NPV works is to take the given array of period end cashflows (20, -2, -2 in your case) and discount them to the start of the first time period. If you have yearly time periods, then providing 10% as the input is fine. If you have monthly time periods, then you will need to adjust the interest rate …

adjusted interest rate = (1 + annual rate) ^ (1/12) – 1

If you want the discounted value at the end of the time period, then multiply the answer by (1+interest rate).

In your example, NPV(10%, 20, -2, -2) gives 15.03. This is the same as 20 / 1.1 – 2 / 1.1^2 – 2 / 1.1^3 (20 discounted for 1 year, -2 discounted for 2 years, -2 discounted for 3 years. Note: you need to enter the signs of the cash flow correctly!

Reply | Quote