Calculating net present value

Ever wonder whether you should invest in a friend’s business idea?

What is the mysterious formula against which businesses, from your local corner shop to Apple Inc. are evaluated?

Today we bring you a simple lesson in financial valuation: net present value (NPV). Imagine your friend Emma told you she needs a £350 loan to start a lemonade stand, which will make £50 profit in the first month, £150 in the second month and £200 in the third month, before she has to close down for winter. She will give you her first three months’ profits to pay you back your loan.

Should you make this investment? (Emma assures you that she will pay you those amounts no matter what.) What if she wanted £380?

Well that depends on what else you would do with the money. You could, of course, spend it but let’s suppose you want to save up for a rainy day and invest it elsewhere, say in a stock portfolio. In that case, you would make the market interest rate: 5% per month (in this magical, hypothetical land!).

Think of this market interest generated from the stock portfolio as a benchmark for comparison. When calculating whether Emma’s lemonade stand is a good idea, you should account for the money lost from not investing in the hypothetical stock portfolio instead. This is called “opportunity cost” and is worked out here by deducting the money you would have otherwise made from the future cash promised by your friend. But to do this exactly, we will have to turn to some maths.

Let’s start simple: in a month’s time (the same time the second repayment is due) that £350 would be worth 150*(1 + 0.05)=£367.5, but we want the opposite of that to account for the opportunity cost. So we simply divide: 1/(1+ 0.05)*350 = £333.33 This is the value of your friend’s second repayment minus the hypothetical money earned from the stock portfolio interest.

The general formula here is: 

NPV = C / (1 + r)^t

Where C is cash in time t (where t=0 is the current month, t=1 is next month, and so on) and r is the interest rate (in our case 5%) of the alternative.

If we go back and apply this to our friend’s business proposal where she asked for £350, we can draw up this table:

So notice that supporting Emma’s lemonade ambitions is a good idea if she asked for £350, but not if she asked for £380, even though from pure addition it seems like that would be a good idea too. This is because we could have easily taken that £380 and either consumed straight away or we could have invested the money and got £418.95 (=380*(1.05)^2) in the third month, so that is almost an extra £20! Or £385.875 (=350*(1.05)^2) in the third month if you invested that £350 now.

This is the basis for evaluation of future revenues, one-off payments or costs and would be applied in the same way, whether in a small business or a large corporation!

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Alan Gurney is AAT Comment’s Excel tips writer.