If you are still not convinced about the importance of the NPV consider the result of making an alternative investment at the safe interest rate of I% using just the negative cashflows (recall that the negative cashflows are what you would have invested to receive the positive element of the cashflow).

This represents what you could have earned on the money you have invested in the "risky" project if you had invested it into a "safe" project at the current safe interest rate.

Of course if you had invested in the safe project then you wouldn't receive any of the positive values generated by the risky project - just the income from the investments at the safe interest rate.

Each such cash sum invested at I% will eventually grow to:

FV=-Si*(1+I)^m

where m is the number of time periods left to the end of the investment. The negative sign is needed simply because of our cashflow convention that an investment of $S represents a cashflow of -S.

This is simply the standard compound interest formula applied to each of the negative cashflows. The value of the entire "safe" investment is just the sum of all such values. It really is just the total you would receive from an alternative safe investment with the same cash outflows.

In other words, the future value of the safe investment FVsafe is just the sum of each negative cashflow compounded (at I%) forwards to the end of the investment.

Now the NFV of the original risky investment is the sum of the NFV of the negative and positive cashflow calculated separately so:

NFV(risk)=NFV(risk+)+NFV(risk-)

where NFV(risk+) is the NFV of the positive part of the cashflow and NFV(risk-) is the NFV of just the negative part of the cashflow.

Again we assume that the interest rate used is the safe rate.

You can also see that theFuture Value of the "safe" investment is i.e. the alternative were you simply invest your inputs, i.e. the negative cash flow items at the safe rate is:

FV(safe) = -NFV(risk-)

that is just the NFV of the negative components of the cash flow.

So putting these two together we can see that if the NFV of the original risky investment is just the NFV of the positive cashflows minus the NFV of the alternative safe investment, that is:

NFVrisk=NFV(risk+)- FVsafe

This gives us a basic relationship between the NFV of the entire cashflow and the NFV of the alternative safe investment.

Notice that this is what the entire risky investment is worth at the end of its term assuming that each item in the cash flow was invested at the safe rate.

Consider what this formula means if the NFV for the original ‘risky’ investment is zero. Then the NFV of the positve cashflow is equal to the total future value of the alternative safe investment.

That is, as:

NFVrisk=NFV(risk+)-FVsafe=0

we have:

NFV(risk+)=FVsafe

Thus you can see that when the NFV, and hence the NPV is zero, then the future value of the positive part of the cashflow is equal to the final value of the safe investment.

However you have to be very careful that you understand exactly what is being compared against what.

The safe investment involves depositing the negative cashflows at I% at the time that they would occur in the original investment. The future value of this investement is equal to the value of the positive cashflows in the original ‘risky’ investment but notice that for this to be true these also have to be invested as they are received at the ‘safe’ interest rate of I%.

Thus this comparison of values still takes into account the time when each sum of money is received. But as long as you do reinvest each sum of money at I% as it is received NFV(risk+) really does represent the amount of money you have received at the end of the risky investment and of course FVsafe is the amount you receive at the end of the safe investment.

When these two quantities are equal the worth of both investments are is the same.

When the NFV and hence the NPV of the risky investment is positive then:

NFV(risk+)>FVsafe

and when it is negative then:

NFV(risk+)<FVsafe

In other words a positive NPV does imply that you make more from the risky investment than a safe investment at I% and a negative NPV indicates that you would be better off making the safe investment.

Borrowing at I%

Working out the NPV of an investment tells you how good it is compared to making a safe investment at I%.

You might be wondering if there is any relationship between making the safe investment at I% and borrowing the money to fund the risky investment at I%?

You can see that this question is raised by the idea of using the market rate for capital as I% in the calculation of NPV.

The reasoning goes something along the lines of:

as the cash sums are discounted by the interest rate I% a positive NPV means that there is a profit to be made over and above the market rate.

However to make this more precise we need to work out exactly how borrowing the money needed for the investment effects the situation.

If we assume that the cash sums received are used to pay off the loan then we can keep a current balance as each time period passes.

Suppose that the cashflow starts with a sum $S1 at the end of the first time period.

This means that the at the end of the first time period the loan account stands at $S1. At the end of the second time period the cashflow received $S2 is added to the loan account along with the interest due to the debt:

S1*(1+I)+S2

At the end of the third time period the loan account stands at:

S1*(1+I)^2+S2^(1+I)+S3

and so on.. You should recognise this as nothing more than the Net Future Value of the entire cashflow calculated using I%.

In other words, the NFV is the profit that you would make on the investment if you borrowed the negative sums in the cashflow at I%.

This gives us another interpretation of the NPV investment decision rule. If the NPV is positive and you can borrow money at I% to make the investment then you will make a profit of NPV*(1+I)^n where n is the duration of the investment.

This interpretation takes our understanding of the NPV and its related FPV one step further and makes the NPV investment rule even more reasonable.

If an investment has a positive NPV then it not only outperforms a safe investment at I%, it gives a positive return even if the investment has to be funded by borrowing an I%.

NPV and NFV - a summary

We now have several good reasons for using the NPV as a measure of the ‘goodness’ of an investment.

A positive NPV calculated at I% implies that:

the investment outperforms a compound interest investment based on the negative part of the cashflows at the same rate of interest.

the investment is still profitable if the negative cashflows have to be borrowed at the same rate of interest.

In both cases it is assumed that it is possible to reinvest at I%.

The NFV also gives the profit made if the investment is funded by borrowing at I%.

Rate differentials

There is no doubt that at this point you may have noticed that there is an unrealistic assumption built into the calculation of the NFV. In calculating the interest paid on the loan and the interest earned on any positive deposits we have used the same rate of interest!

It is a well known fact that the rate for borrowing isn’t the same as the rate for a saving. In most cases this difference can be ignored but if you want a completely accurate answer then you really do need to calculate a ‘modifed’ NFV using appropriate rates for the positive and negative sums.

If you do this in for the example earlier and assume that the rate for borrowing is 10% and that for a deposit is 8% then the appropriate calculation is

=NPV(8%,B3:B6)*(1+8%)^3+B2*(1+10%)^4

and the result is -$462.72.

In other words, if you take into account a 2% differential in interest rates for borrowing and saving then the investment that previously showed a profit now shows a loss.

Notice that in this case the NPV is unaffected by an interest rate differential because the negative sum is borrowed at the start of the investment - but this is not generally true.

Also notice that although the investment now makes a loss if the money has to be borrowed at 10% it still makes a better return than investing the same sum of money at 8%.

In conclusion:

if you have to borrow at more than I% to fund an investment then a positive NPV isn’t a guarantee that you will make a profit.

Next we need to look at the most sophisticated measure of an investment's worth - IRR.

Financial Functions

Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.

Understanding Percentages Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.

Interest Simple and Compound We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment. The ideas of Present and Future Value PV and FV are introduced.

Effective Interest Rates We explore the idea of the `effective’ annual interest rate and then on to the Effective Interest Rate/Annual Percentage Rate, the much quoted EIR or APR.

Introduction to Cashflow - Savings Plans In the first of three chapters covering the way in which interest rate affects cashflow we explore savings - but first we introduce some general ideas that apply equally to annuities and repayment loans.

Cashflow Continued - Annuities We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects

Exploring Repayment Loans Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.

Present and Future Values The principles of present and future value apply even if the cash flow is irregular. The calculations are just a matter of breaking down the cash flow calculations into simple steps.

Investment Analysis How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.

Advanced Investment Analysis IRR and MIRR The IRR is perhaps the most complicated of the measures of the value of an investment with an irregular cash flow. Understanding exactly what it means is a good step toward making correct use of it.