In this article we'll look at four of the essential financial functions available in Excel 2010. These are Future Value, Present Value, Net Present Value and Payment.

Future Value Function: FV

Suppose a bank savings account offers 2% interest per year paid monthly. We decide to save over a 5 year period £100 a month. The Future Value tells us the amount the savings have grown to at the end of the five years.

In Excel 2010 the Future Value function takes the form =FV(rate, periods, amount). So type the interest rate 2% in cell D4, the number of years 5 in cell E4 and the monthly savings £100 in cell F4.

There are an additional two optional parts in the FV function separated by commas after the first three required parts. If you want to deposit an initial lump sum as well as your regular monthly savings, the amount is entered as a fourth element. And if you get the interest paid at the start of each month rather than at the end, you add a fifth element 1 before the closing brackets. Both are assumed to be 0 (zero) if not entered.

We'll enter the Future Value function into cell G4. In using financial functions in Excel 2010, always ensure the timescale is consistent throughout the function. As interest is paid monthly we'll ensure each part of the FV function is in months. So rate is d4/12, the number of periods is 5 x 12 months and the monthly savings amount is £100.

To calculate the Future Value n cell G4 type =FV(D4/12,E4*12,F4) and press Enter. This should give the Future Value as £6304.74. Suppose you want to pay in a £500 lump sum at the start. To see the future value now, just edit the FV function to show =FV(D4/12,E4*12,F4,500). This should show the new future value as £6,857.28.

Present Value Function: PV

Suppose you want to buy a payment plan (often called an insurance annuity) from a savings pot you've built up, which will pay you a fixed amount of £200 each month for a specified number of years. We discount future payments because money loses value over time, at an annual rate of 3%. The Present Value function will calculate what the series of future payments is worth today, so you can compare this with the cost of the plan.

In Excel 2010 the present value looks like =PV(rate, periods, amount). So type the rate as 3% in cell D4, the period as 25 in cell E4 and the payment amount £200 in cell F4.

To calculate the Present Value n cell G4 type =PV(D4/12,E4*12,F4) and press Enter. This should give the Present Value as £42,175.29. So if the pot payment is £40,000 or less then the plan will be good value assuming a rate of 3%.

Net Present Value Function: NPV

Suppose you are considering investing £10,000 in a new project which will last 3 years. The forecasted yearly incomes are £4000, £5000 and £4,000, and the discount rate is 3%. The Net Present Value is a sum of the discounted incomes, minus the original investment. A positive NPV suggests the project is worthwhile whereas a negative NPV suggests an overall loss will be made.

In Excel 2010 the net present value function looks like =NPV(rate, value1, value2, value3) where rate is the annual discount rate, and value1, value2, value3 are the individual period incomes . So type the rate as 3% in cell D4, value1 in cell E4 as £4,000, value2 in cell F4 as £5,000 and value3 in cell G4 as £4,000. We can type the original investment of £10,000 into cell H4.

We'll calculate the NPV in cell J4, so type =NPV(D4,E4,F4,G4) - H4 and press Enter. This should give you the Net Present Value as £2,257.04, so based on these figures the project is worthwhile.

Payment Function: PMT

We want to work out the monthly repayment on a mortgage or principal taken out for £100,000 over 20 years at 3% annual interest. The Payment PMT function calculates monthly payments assuming constant payments and constant interest rates.

In Excel 2010 the Payment Function looks like =PMT(interest, periods, principal). So this time type the interest rate 3% in cell D4, the period as 25 (years) in cell E4 and the amount borrowed or principal £100,000 in cell F4.

To calculate the fixed monthly payment in cell F4 type =-PMT(D4/12, E4*12, F4) and press Enter. The monthly payment will be shown as £474.21

The four Excel 2010 financial functions described in this article, Present Value, Future Value, Net Present Value and Payment are often the first financial functions to learn under this category. To really learn much more about financial functions in Excel 2010 consider attending one of the specialised Excel 2010 courses available. The best ones are often tailored to your individual needs.