Time Value of Money in Excel: 5 Essential Functions

To calculate the time value of money in Excel, use five built-in functions: PV (present value), FV (future value), PMT (payment), RATE (interest rate), and NPER (number of periods). Enter the amounts you know, keep the rate and the periods in the same time unit, and give cash paid and cash received opposite signs.

The time value of money is the idea that a dollar today is worth more than a dollar received later, because today’s dollar can be invested to earn interest. Accountants use it to measure bonds, long-term notes, and leases. In the FASB Accounting Standards Codification, for example, ASC 835-30 covers imputed interest on notes, and ASC 842 measures a lease liability at the present value of the lease payments.

This practical guide is written for Intermediate Accounting students and for CPA candidates who are reviewing present value. Excel makes repeated calculations and changes in assumptions easier, but the cash-flow timeline still determines the correct inputs.

Key takeaways

• Excel solves time value of money problems with five functions: PV, FV, PMT, RATE, and NPER. Each one solves for a different unknown.
• The rate and the number of periods must use the same time unit. For monthly payments, divide the annual rate by 12 and multiply the years by 12.
• Cash you pay out is negative and cash you receive is positive. A negative answer is normal, not an error.
• Type 0, the default, means payments at the end of each period (an ordinary annuity). Type 1 means payments at the beginning (an annuity due).
• One PV function can price a bond: =PV(market rate, periods, interest payment, face amount).

Which Excel functions calculate the time value of money?

Select Formulas → Insert Function, or type a formula directly into a worksheet cell. In the syntax below, square brackets identify optional arguments; do not type the brackets into your formula.

These functions use a constant rate and, when payments occur, equal payments at regular intervals. This lesson addresses single sums, level annuities, a loan payment, and a bond with fixed payments. Microsoft documents the PV, FV, PMT, RATE, and NPER arguments.

Set up periods, signs, and payment timing

Here are the six inputs at a glance.

Rate is the interest rate for one period. Nper counts those same periods. A nominal annual rate of 6%, compounded monthly over three years, uses 6%/12 and 3*12. An annual rate of 6% with annual compounding uses 6% and 3.

A nominal annual rate is simply the quoted yearly rate, before you split it into periods. Here is the difference in numbers. Invest $12,000 for three years at 6% compounded monthly, and =FV(6%/12,3*12,0,-12000) returns $14,360.17. With annual compounding, =FV(6%,3,0,-12000) returns $14,292.19. Same rate, same three years, and about $68 more, only because interest is added 36 times instead of 3. For more practice, see compounding periods.

Pmt is the equal recurring payment. Use zero for a single sum. Pv is the amount at the starting date; fv is the amount at the ending date. Write zeros explicitly in unused positions to make your formula easy to check.

Choose one perspective. For an investor, a deposit is negative because cash leaves the investor; a later withdrawal is positive because cash returns. A negative PV result can therefore represent the investment required today, rather than an error. When a question asks what the asset is worth, report the amount as a positive number.

Type = 0 means end-of-period payments, an ordinary annuity. Type = 1 means beginning-of-period payments, an annuity due. Omitting type selects 0. Review the ordinary annuity versus annuity due comparison if the timing is unclear.

How do you calculate the future and present value of a single sum?

A single sum is one amount of money that is paid or received once, with no payments in between. For the formulas behind the two functions in this section, see future value of a single sum and present value of a single sum.

Hypothetical example. Cedar Studio invests $12,000 for four years at 5% compounded annually. It makes no additional deposits.

=FV(5%,4,0,-12000,0) returns $14,586.08. The initial investment is negative and the future balance is positive. The underlying check is $12,000 × (1.05)^4 = $14,586.08. You can type that check into Excel as =12000*1.05^4.

Working backward, =PV(5%,4,0,14586.075,0) returns −$12,000. The future receipt is positive, so Excel reports the present cash outflow needed to obtain it. Use the unrounded future amount when checking the reverse calculation.

Use cell references for reusable work

Enter 5% in B2, 4 in B3, and 12000 in B4. Then enter =FV(B2,B3,0,-B4,0) in B5. Label each input and shade B2:B4 light blue. Changing B2 changes the result without rewriting the formula. Keep input cells separate from calculated cells.

How do you find the interest rate or number of periods?

For Cedar’s same investment, =RATE(4,0,-12000,14586.075) returns 5% per year. Format the answer as a percentage. Type the full function name, RATE. A textbook formula may call the rate i or r, but Excel only recognizes RATE.

=NPER(5%,0,-12000,14586.075) returns 4 periods. Because this example uses annual periods, that means four years. A monthly setup would return months.

RATE uses an iterative calculation. Its optional guess supplies a starting rate; the default is 10%. If it returns #NUM!, first verify signs and inputs, then try a reasonable different guess. For this investment, the annual rate is 5%. The guess tells Excel where to start. For some cash flows, different guesses can lead to different solutions. For the underlying reasoning, see finding the unknown rate or time in single-sum problems.

Compare ordinary annuities and annuities due

An annuity is a series of equal payments made at regular intervals. For the formulas behind these functions, see future value of an ordinary annuity, present value of an ordinary annuity, and present value of an annuity due.

Hypothetical example. An investor deposits $2,000 each year for four years at 5%. At the end of Year 4, the balance depends on whether each deposit occurs at the year’s beginning or end.

The annuity due receives one additional year of interest on every deposit. Its balance equals the ordinary-annuity balance × 1.05.

For four annual receipts of $2,000, =PV(5%,4,2000,0,0) returns −$7,091.90. Changing type to 1 returns −$7,446.50. The negative signs represent the equivalent investment today; the beginning payments have the larger present-value magnitude.

How do you calculate a loan payment with PMT?

PMT finds the equal payment that pays off a loan, or builds up to a savings goal, over a set number of periods.

Hypothetical example. You borrow $20,000 for a car at a 6% annual rate and repay it monthly for five years. Payments are monthly, so both inputs must be in months. The rate is 6%/12 and the number of periods is 5*12.

=PMT(6%/12,5*12,20000,0,0) returns −$386.66. The loan amount is positive because you receive that cash today. The payment is negative because cash leaves you every month.

A common mistake is to leave the rate annual. =PMT(6%,5*12,20000,0,0) returns −$1,237.51, which is far too high, because Excel treats 6% as the rate for each month. If a payment looks unreasonable, check the rate first.

According to Microsoft, the PMT result covers principal and interest only. It does not include taxes or fees.

How do you price a bond with one PV function?

A bond is a loan that investors make to a company. The stated rate, also called the coupon rate, sets the cash interest the bond pays. The market rate is the return investors demand today, and it is the rate you discount with.

Hypothetical example. A four-year bond has a $20,000 face amount, a 4% stated annual rate, annual year-end interest, and a 5% market rate. Annual cash interest is $20,000 × 4% = $800.

=PV(5%,4,800,20000,0) returns −$19,290.81. An investor pays $19,290.81 today to receive the interest and principal. Both future inflows are positive. Use the market rate to discount; use the stated rate only to determine cash interest. The price is below face amount because the stated rate is lower than the market rate. See the bond pricing explanation for the two-component calculation.

Semiannual bonds: match the video

Most bonds pay interest twice a year, so divide the annual rates by 2 and multiply the years by 2. The video near the top of this page prices a $1,000, 30-year bond with an 8% annual coupon when the market rate is also 8%. Each coupon is $1,000 × 8% ÷ 2 = $40, there are 30 × 2 = 60 periods, and the market rate is 4% per period.

=PV(4%,60,40,1000,0) returns −$1,000.00. The price equals the face amount because the stated rate and the market rate are the same. The video gets there in two steps: the 60 coupons are worth $904.94 today, the $1,000 principal is worth $95.06 today, and together they equal $1,000.

Common Excel errors to avoid

• Entering 5 instead of 5% makes Excel use 500%.
• Mixing a monthly rate with years understates the number of periods.
• Using pmt for an initial deposit repeats that amount every period.
• Changing the sign on only the interest or only the face amount of a bond makes Excel treat one of them as cash you pay, and the price comes out wrong.
• Rounding intermediate factors can create differences from Excel’s full-precision result.

For another way to verify the same cash flows, follow the BA II Plus time value of money guide.

CPA Exam tip. You will not have Microsoft Excel on the CPA Exam. The exam software has its own built-in spreadsheet tool instead. Practice with the AICPA sample tests before exam day, and make sure you can also solve these problems with the present value formulas, so you are not depending on one tool.

Three practice questions with answers

All practice scenarios are hypothetical. Round final dollar answers to cents.

1. One deposit

What does $5,000 become after three years at 4% annually?

2. Payment timing

How do you value five $1,000 receipts arriving at the beginning of each year at 6%?

3. Find the rate

A $10,000 investment becomes $12,100 in two years, without intermediate payments. What is the annual return?

Want more guided practice? Continue with the Intermediate Accounting course, or try the free sample course first.

Frequently asked questions

What are the time value of money functions in Excel?

The five main ones are PV, FV, PMT, RATE, and NPER. Each one solves for a different unknown: present value, future value, the payment, the interest rate per period, or the number of periods. You enter the amounts you know, and Excel returns the one you are missing.

How do I calculate present value in Excel?

Use the PV function: =PV(rate,nper,pmt,fv,type). For example, $10,000 that you will receive five years from now, discounted at 6% a year, is =PV(6%,5,0,10000). Excel returns −$7,472.58, which means you would pay $7,472.58 today to receive that amount later.

Why is my Excel present value negative?

Excel treats money you receive as positive and money you pay as negative. If you enter future receipts as positive numbers, PV shows the amount you would pay today as a negative number. It is not an error. When a question asks what the asset is worth, report the amount as a positive value.

Does NPER always return years?

No. It returns periods consistent with the rate and payment interval. Divide a monthly result by 12 to express it in years.

Why does Excel differ slightly from a factor table?

Printed factors may be rounded. Excel calculates with more precision. Keep full precision during the calculation and round the final answer as instructed.

What is the difference between PV and NPV in Excel?

PV works when every payment is the same amount, and it lets you choose payments at the beginning or the end of each period. NPV handles payments of different amounts, but it assumes each one arrives at the end of a period. If a cash flow happens today, add it to the NPV result separately.

How do I use monthly or semiannual periods in Excel?

Divide the annual rate by the number of periods in a year, and multiply the years by that same number. For monthly payments on a five-year loan at 6%, enter 6%/12 for rate and 5*12 for nper. For a bond that pays interest twice a year, divide the annual rates by 2 and multiply the years by 2.

About the author. Mansour Farhat, CPA, CIA, DBA, is the professor behind Farhat Lectures, an online learning platform for accounting students and for CPA, CMA, and EA exam candidates.