NPV formula error?

I notice that H2 and I2 both contain the number 5. I think I2 should be 6. If I put 6 in I2, then the red and blue cells are the same.

I am not skilled enough in financial calculations to know where the yellow values could have come from.

Short but useless answer -- Yes, I am certain that there is some way to calculate what you want. As I noted, I do not know business finance, so I don't know what it means to assume that the model ends at the end of year 5, or why your original sheet includes two entries for year 5, or how this figures into NPV(). If you can help me understand the business math side of the question, then I expect I can help with the Excel side of the question. If not, then we will need to wait until someone more knowledgeable about the business math side of the question can provide some input.

Originally Posted by MrShorty

Short but useless answer -- Yes, I am certain that there is some way to calculate what you want. As I noted, I do not know business finance, so I don't know what it means to assume that the model ends at the end of year 5, or why your original sheet includes two entries for year 5, or how this figures into NPV(). If you can help me understand the business math side of the question, then I expect I can help with the Excel side of the question. If not, then we will need to wait until someone more knowledgeable about the business math side of the question can provide some input.

I hope this example helps:

I invest $100 to buy an apartment. In return, I get $10 of rent at the end of every year; and finally, at the end of 5 years, I can still sell the apartment for $70

So here’s the cash I put in (or get out) from the deal:

Year 0: -100 (because I have to put up the money first to buy it)
Year 1: +10 (first annual rent)
Year 2: +10
Year 3: +10
Year 4: +10
Year 5: +10
Residual Value: +70

But also, at the end of Year 5, I don’t just collect the rent for that year. I also get to sell the apartment for $70.

So after “Year 5”, there is:

Residual Value: +70

In effect, this $70 comes in at the same time as the $10 rent at Year 5. So effectively, you can put “Year 5: +80”. It’s just a matter of formatting that some people (including the creator of this spreadsheet) choose to separate out the $70 and the $10 you get at the end of Year 5.

And what I need to do right now is to figure out a way to do an IRR calculation on this series. What I think is happening is that the IRR formula thinks the "Residual Value: +70" occurs in Year 6 as opposed to Year 5. Is there a way to correct this?

But, isn't that the same as receiving $80 for year 5? The NPV() function assumes that each entry is a for a new period, it does not have a way to know that 2 entries apply to the same year/period. You need to combine everything for each year into a single entry.

So, in your original file, if I replace H3 with 24049.75 (the sum of 18174.75+5875) and clear I3, then both the red and blue cell return 32105, which is really close to the 32104 in the yellow cell.

As for the IRR(), isn't the value in the green cell correct? According to the help file, IRR() is supposed to be the interest rate that causes the NPV to be 0. If I put 0.5855 into B5, then I get a negative value in D8 and E8. If I put 0.4815 into B5, then D8 and E8 are both close to 0. (I could probably put this kind of thing to Solver or Goal Seek, if I wanted to).