What is the Excel PV function?
The PV function on Excel is a financial function useful in investment calculations. It is used to get the present value of a series of future payments in today’s pounds/dollars/euros based on the assumption that payments are periodic and constant and are at a constant interest rate. It can also be used to get the original amount of a loan.
- rate – This is the interest rate per period.
- nper – total number of payment periods.
- pmt – payment made in each period.
- fv – [optional] The cash balance you want to achieve after the last payment has been made. If omitted, fv is assumed to be zero.
- type – [optional] time when payments are due. 0 = at the end of the period, 1 = at the beginning of the period. The default is ‘0’.
This function helps to returns the present value of an investment.
How to use the Excel PV function to find original loan value
For this example, we would use the PV function to find the original amount of a loan that is currently being paid back at an annual interest of 4.5%, a payment period of 60 months and a constant periodic payment of £93.22
The following steps should be taken;
- Open the spreadsheet containing the parameters and value relating to your loan.
Figure 1. Table of parameters
- Click on the cell where you want the loan amount to be displayed (C10).
Figure 2. Click cell for the final result
- Insert the formula
=PV(C5/12,C6,C7)into that cell or into the formula bar above.
Here, C5/12 represents the rate (interest rate per period). We divide the value in C5 (annual interest of 4.50%) by 12 to get the interest rate per period. C6 is our nper as the number of periods (60) comes from that cell. Our pmt is £93.22 which is found in cell C7.
Figure 3. Insert formula
- Press Enter. Your original loan was about £5,000.
Figure 4. Press Enter for the result
- A stream of cash flows that includes the same amount of cash outflow (or inflow) for each period is an annuity. A mortgage or a car loan or is an annuity.
- In annuity functions, cash that you pay out, such as a deposit to savings, is denoted by a negative number; cash that you receive, e.g. a dividend check, is represented by a positive number. For example, a £5,000 deposit to the bank would be represented by the argument –£5,000 for pmt if you are the depositor, and by the argument £5,000 for pmt if you are the bank.
- By convention, dividends are input as positive values while payments are input as negative values in the PV function.