1.13 PV Function

The PV function in Excel computes the present value of a series of cash flows, given a set of parameters that you input into the formula. In order to use the PV function, you need the interest rate to be used in the calculation, the number of periods (e.g., days, months, years, etc.), and either the periodic payment amount or the future value amount, or both. You will also need to inform Excel regarding the timing of payments (i.e., do they occur at the end of each period or the beginning of each period). When it comes to bonds, you will typically need to calculate the periodic payment amount using the information provided to you. Bonds are also a situation which includes BOTH periodic payments AND a future value. The image below demonstrates how to use the PV function in Excel, when the parameters are provided to you.

Note from formula in cell C8 that the first piece of information you need to input in the PV function is the rate, in this case the rate is stored in cell C2. The rate is the interest rate used in the calculation of the present value. In present value calculations, it is often referred to as the discount rate. The second piece of information you put into the PV function is the number of periods. You will need to make sure that the number of periods, the interest rate, and the periodic payment, the third piece of information in the function, are all using the same frequency. In other words, if you receive monthly payments, you need to use a monthly interest rate and the number of periods also needs to be expressed in terms of months (i.e., the number of months). If instead you receive annual payments, you should use an annual interest rate and the number of periods should be expressed in terms of the number of years.

The fourth piece of information in the function is the future value. This is a lump sum cash flow that occurs at the end of the series of cash flows. Whereas the payment parameter is a regular cash flow of the same amount each period, the future value is a lump sum which occurs only one time at the end of the term. The final piece of information to be inputted into the PV function is the type. This refers to the periodic cash flows, and is an indicator of whether the periodic cash flows occur at the end of each period or the beginning of each period. For example, if the bond issuer pays interest at the end of each period, you would indicate that the type is end of period payments.

One final note on the PV function is that Excel uses positive numbers to represent cash inflows, and negative numbers to indicate cash outflows. In this example, all numbers are positive, indicating that the person calculating the bond value (the PV of the bond) will be paying cash now, and receiving periodic cash flows as well as a lump sum cash flow in the future. Because of this assumption, the answer returned will be negative, representing a cash outflow now. In order to show a positive number, showing a cash inflow now and cash outflows for periodic payments and for the lump sum payment later, you will need to either put a negative sign in front of the PV function "=-PV(...)" or you will need to put negative signs in front of both the pmt and fv amounts.