**YOUR OBJECTIVE**

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 (**P**resent **V**alue) 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 **n**umber of interest **per**iods 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 **p**ay**m**en**t**s 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 **F**uture **v**alue, i.e., the *Target valu*e 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**.

As you can see in the following illustration, you would have to deposit $22,417.75 now to accrue $30,000 at the end of the five years.

**(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 Wizard*by 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.

Kein Banner zum Anzeigen