Investments: FV, NPV and IRR practice exercises

What savings grow to, what future cash flows are worth today and the return an investment makes: the three formulas behind most finance test questions.

Start the first exercise

How FV, NPV and IRR work

These three functions answer the main questions of investing. FV tells you what savings grow to, from a rate, a number of periods and a deposit. NPV tells you what future cash flows are worth today at a discount rate. IRR returns the yearly rate of return an investment earns from its cash flows.

For a savings plan with 500 a month at 5% a year for 10 years, =FV(B3/12,B4*12,-B2) returns 77,641.14. The deposit has a minus sign because it is money going out. For a project with -10,000 in year 0 (B2) and 3,000, 4,000, 5,000 and 6,000 in years 1 to 4 (B3:B6), =NPV(E1,B3:B6)+B2 at 8% in E1 returns 4,586.47, and =IRR(B2:B6) returns 24.9%.

The classic NPV mistake is the first value. NPV treats it as one period from now, so leave the year 0 investment out of the range and add it afterward, as above. IRR is the opposite: it needs the year 0 investment as its first value, plus at least one positive cash flow. The exercises below cover a savings plan with FV, net present value and the rate of return with IRR.

  1. 1What savings grow to with FVIn B5, work out what the savings grow to with FV: the monthly rate, the number of months and the monthly deposit as money going out, FV(B3/12,B4*12,-B2).
  2. 2Net present value with NPVPremiumIn E2, work out the project's net present value at the rate in E1. NPV discounts every value it gets, so give it years 1 to 4 and add the year 0 investment after: NPV(E1,B3:B6)+B2.
  3. 3Rate of return with IRRPremiumIn E1, work out the project's internal rate of return with IRR. Give it every cash flow, starting with the year 0 investment.

Functions in this topic

Questions

How do I calculate compound interest in Excel?

Use FV with a single deposit and no regular payments: =FV(B3,B4,0,-B2). With 1,000 in B2, a 5% rate in B3 and 10 years in B4, it returns 1,628.89. If the interest is added monthly, use B3/12 and B4*12 instead.

Why does IRR return #NUM!?

IRR needs at least one negative and one positive cash flow, such as an investment followed by income. Check the signs first. If they are fine, Excel may need a starting guess as the second argument, as in =IRR(B2:B6,0.1), so it can home in on the rate.

What does a negative NPV mean?

The project earns less than the discount rate you used, so it does not pay off at that rate. A positive NPV means it earns more. The IRR tells the same story: when the NPV is above 0, the IRR is above the discount rate.