PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.
PV formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
- rate (required):
The interest rate per period. This should be expressed as a decimal or percentage, depending on the value of the
- number_of_periods (required):
The total number of payment periods in the investment or loan.
- payment_amount (required):
The payment made each period. This should be a negative value when the user is making payments, and a positive value when they are receiving payments.
- future_value (optional):
The lump sum amount that the user expects to receive in the future. This is an optional parameter and defaults to 0 if not specified.
- end_or_beginning (optional):
Indicates whether payments are due at the end or beginning of each period. This is an optional parameter and defaults to 0 if not specified. A value of 0 or omitted means payments are due at the end of the period, while a value of 1 means payments are due at the beginning of the period.
ExamplesHere are a few example use cases that explain how to use the
PV formula in Google Sheets.
Calculating the present value of a loan
PV function can be used to calculate the present value of a loan, based on the interest rate, number of payments, and payment amount. This can help users determine how much they can afford to borrow, or how much they will owe in total.
Determining the value of an investment
Users can use the
PV function to determine the present value of an investment, based on the expected future cash flows and the interest rate. This can help them decide whether the investment is worth making, or how much they should pay for it.
Comparing different investment options
By using the
PV function with different interest rates, payment amounts, and number of periods, users can compare the present value of different investment options. This can help them choose the option that will provide the best return on investment.
PV not working? Here are some common mistakes people make when using the
PV Google Sheets Formula:
Incorrect order of inputs
Users sometimes enter the inputs in the incorrect order, which can cause errors in the calculation. Make sure to enter the inputs in the correct order as specified in the formula syntax.
Missing required inputs
Users sometimes forget to include one or more of the required inputs, which will cause the formula to return an error. Make sure to include all required inputs in the formula.
Incorrect use of negative values
Users sometimes use negative values for inputs where only positive values should be used, or vice versa. Make sure to understand the meaning of each input and use the correct sign for each value.
Using inconsistent units for inputs
Users sometimes use inconsistent units for inputs, such as using annual interest rates with monthly payment amounts. Make sure to use consistent units for all inputs, or adjust the inputs accordingly to match the desired units.
Not understanding the meaning of the result
Users sometimes misunderstand the meaning of the result, which can lead to incorrect interpretation of the calculation. Make sure to understand what the result represents and how to use it in the context of the financial analysis.
The following functions are similar to
PV or are often used with it in a formula:
FVfunction calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.
RATEformula returns the interest rate per period of an annuity. This formula is often used in financial analyses to calculate the rate of return on an investment. It assumes that payments are made at regular intervals and that the interest rate remains constant throughout the duration of the annuity.
NPVfunction calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.
IRRfunction is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects.
You can learn more about the
PV Google Sheets function on Google Support.