Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

Calculating periods

The NPER function is used to determine how many payments are necessary to pay off a loan, or

to fund an account a certain amount. Its syntax is

NPER(rate, pmt, pv, fv, type)

Years until retirement

If you know how much money you need to retire and you’re making regular payments to a

retirement account, you can use the NPER function to determine the age at which you can retire.

Assume you’ll need $500,000 to retire, and you’re contributing $100 per month. Further assume

that your retirement account has a balance of $350,000. This formula returns the number of

years until you can retire:

=NPER(10%/12,–100,–350000,500000,0)

Assuming you can earn 10% on your investments, NPER returns 41.8 months (or 3.5 years). You

can combine NPER and PV if you know how much you need to live on each week, as in this

formula:

=NPER(10%/12,–100,–350000,PV(.1/52,20*52,–1000,0,0),0)

The PV function used in the fv arguments assumes that you’ll make 10% (converted to weeks),

that you’ll need to withdraw money for 20 years (converted to weeks), that you’ll need $1,000

per week, and that there will be nothing left. If you can live on $1,000 per week, you can retire in

2.4 years.

The two formulas in this section are shown in Figure 11-12.

Figure 11-12:
Using the PERIOD function for retirement calculations.