Introduction
When I discuss net present value and present value in my classes, my books and my articles, I always demonstrate them from first principles. Even for delegates and readers who have studied discounted cash flows before, my reminder or my method always shows something new.
Single Cell Solutions
In this post, I want to show you how to find the NPV or the PV of a stream of earnings where both the data and the answer are contained in a single cell.
At the outset, I have to say that I am using Office 365 but if you are using older Excel versions, you will probably have to array enter your answers rather than just pressing Enter.
The Example
I am looking forward to receiving these cash amounts at the end of year 3, year 4 and year 5 respectively: 500, 500, 1000 and the interest rate will be 7% compounded annually.
We can use the NPV() function or the PV() function for this example and notice how the fv values have a minus sign in front of them
NPV(rate,value1,value2,value3...)
=NPV(0.07,{0,0,500,500,1000})
=1502.58
PV(rate,nper,pmt,[fv],[type])
=SUM(PV(0.07,{1,2,3,4,5},,{0,0,-500,-500,-1000}))
=1502.58
If you want to, you can leave off the SUM() and see this answer:
0.00 ... 0 ... 408.15 ... 381.45 ... 712.99
but that's not a single cell answer but it is telling you the present value on a year by year basis, in case you need to know that
Hard Coding
Do notice that everything is hard coded in those formulas and apart from the rate, they have to be: you could create a link to the rate and everything would still work but you cannot put a cell link into the array constants.
There you are, nothing to download as all you have to do is to type and there's your answer!
Duncan Williamson
25th January 2022
No comments:
Post a Comment