Five years from now, your company intends to make a $30,000 investment. First you were asked to calculate how much money must be invested now, so that in five years the $30,000 will be available. As an alternative, you were then asked to calculate how much would have to be invested as a lump sum now if the company were to make additional monthly payments during the entire five years. You can use the excel value PV function to make both calculations. This learning module will show you how to use the excel value PV function to make such calculations.
Excel value: HOW DOES IT WORK?
Given a fixed interest rate, the excel value PV function (Present Value) calculates how much money you would have to invest today to accumulate a certain amount of capital by a specific time in the future. Some think of the excel value PV function as being an inversion of the PMT function.
In this learning object, you will use the excel value PV function to calculate (1) how much money you would have to set aside in an interest bearing account now (present value, Cash value) to obtain a certain amount of money in the future (Future value, Target value). After that, (2) you will make another, similar calculation, but this time you will pay in a smaller amount initially, followed by regular payments into the account over the entire savings period.
(1) Given the annual interest rate available to you and the number of years you intend to keep the investment, begin by clicking in cell B7 where you want the result to appear. (Refer to the above illustration.) Then start the Function Wizard by clicking the button next to the formula bar. After finding and selecting the excel value PV function in the Financial category, click OK. The Function Arguments dialog box will open:
The Rate argument indicates the interest rate per period . Because interest rates are usually stated on an annual basis, to obtain a monthly rate, you would divide this value by 12. In the current example, no interim payments will be made; therefore, no conversion to months is required. For the current example, just enter a cell reference to B4.
Nper indicates the total number of interest periods during an investment . This is the period in which interest will be credited to the account, sometines a year, sometimes monthly. If interest were credited to the account only once a year, the value of this argument would simply correspond to the number of years until the Future value is attained. To indicate the the five annual interest periods in the current example, just click cell B5. Remember, if interest were credited on a monthly basis, you would have to multiply the number of years by 12. (Note: the Rate and the Nper nust both refer to the same periods, whether they are years, months or quarters, etc.)
The Pmt argument indicates the regular payments you want to make each period. By making regular payments during the lifetime of the investment, you reduce the amount you have to invest initially.
Fv specifies the Future value, i.e., the Target value of the investment. When your intention is to accrue capital, this is the amount you want to have in the end. In the current example, enter a reference to cell B3; it contains your savings target of $30,000.
The Type argument indicates when payments are due. If you enter 0 (zero) or leave the argument empty, payments are due at the end of each payment period. If payments are due at the beginning of the payment period, enter 1.
When you are satisfied with the arguments you entered, finish by clicking OK.
(2) Now you can compute the present value (current cash value) using the monthly payment method. First select cell B13. This is where you want to display the result. Now start the Function Wizardby clicking the button next to the formula bar. Then select the excel value PV function from the Financial category. The Function Arguments dialog box will open.
For the Rate argument, enter a reference to B4. This time, because you will be making monthly payments, you have to divide the annual rate in B4 by 12, i.e, B4/12. For the Nper argument, enter a reference to B5 and multiply by 12 to arrive at the total number of months; in this example, this is 5 years times 12 months, i.e., B5*12. For the Pmt argument, click cell B12; this value is negative because it represents capital outlay. Now, for Fv, the future value or cash balance that you want to attain, enter a reference to B3.
When you are satisfied with the arguments you entered, finish by clicking OK. Although we chose the amount of monthly payments ($250), you can choose a payment would best suit your saving’s plan. The result shows that if you invest $9,309.78 immediately (B13) and then make additional payments of $250 per month (B12), at the end of five years (B5), you will have accrued $30,000 (B3).
When using the excel value PV function, capital outlay is always expressed as negative numbers.