How to Calculate a Present Value Using Microsoft Excel

Using Microsoft Excel to calculate the present value of a potential investment is a simple task once you learn the syntax of the required formula. Follow these easy steps and you can calculate present value using Microsoft Excel easily and quickly.

Difficulty: Moderately Easy

Instructions

1. Understand the concept of present value. Present value is one of the Time Value of Money calculations. Use it to answer questions such as, "What is the most expensive car or house I can buy, given the market interest rate and the maximum monthly payment I can afford?" In other words, it is what a given investment is worth today, at this time.

2. Open Microsoft Excel. Click in the cell in which you wish the result of your formula to show. Type the following formula starter: =PV(

3. Observe the screen tip that pops up as soon as you type your opening parenthesis. It looks like this: (rate, nper, pmt, [fv], [type]). This screen tip design will help you know what values to input.

4. Enter the interest rate of your investment, divided by the number of times per year you expect to owe payments. For example, if you can get an interest rate of 6 percent on a car loan paid back monthly, enter ".06/12" (without the quotations) in the rate section of the formula. Type a comma to move to the next section of the formula.

5. Type in the number of periods (nper) you expect to hold the investment. If your proposed car loan is for five years, enter 60 in this field, because the number of periods is 5 years times 12 months, for a total of 60 car payments. Type a comma to move to the next section of the formula.

6. Fill in the highest monthly payment (pmt) you would like to make preceded by a minus sign (since the payment is money leaving your pocket, it is expressed as a negative). If you would like to pay no more than $500 per month, enter "-500" (minus the quotations). Type the ending parenthesis of the equation and press "Enter." Note you do not have to fill in the "fv" or "type" fields; they are optional.

7. Note the results of your equation. If you want to pay $500 per month at 6 percent interest over 5 years, the most expensive car you can buy would cost $25,862.78. Try a second problem to make sure you have the hang of the PV equation in Excel. You want to purchase a house that requires quarterly payments. You want to spend no more than $5,000 per quarter. Prevailing market interest rates are 7.5 percent for a 30-year loan. What is the most expensive house you can afford? Your formula should look like this: =PV(.075/4, 30*4, -5000). You should get $237,968.50 as an answer.