What it does – It calculates the present value of an investment or how much should one invest to attain a predefined future value
Syntax:=PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])rate – The interest rate. The interest rate should be constant for the entire period. Also, this interest rate parameter takes an annual rate. So if in your case, the interest is monthly, then divide the value by 12 and if the interest rate is quarterly, then divide the value by 4.number_of_periods – The number of payments to be made. Payments should be made in every period. Also, this parameter takes an annual period. So if you are going to pay monthly, multiply the value by 12, and if you are going to pay quarterly, multiply the value by 4.payment_amount – The amount per period to be paid. This parameter takes monthly amounts as input. So if you are going to pay annually, then multiply the value by 12, and if you are going to pay quarterly, then multiply the value by 3.future_value – [ OPTIONAL ] – The future value remaining after the final payment has been made. As discussed earlier, this value is the final amount of the investment after the completion of the final monthly/quarterly/yearly payment.
end_or_beginning – [ OPTIONAL – 0 by default ] – Whether payments are due at the end (0) or beginning (1) of each period.
Sample usage:=PV(7/4, 5, 5000, 0, 0)
//This calculates the total amount to be invested quarterly for a period of 5yrs and 7% annual interest rate
What is the PV function in Google Sheets?
PV stands for Present Value. The PV function in Google Sheets is used to calculate the present value of an investment based on a constant interest rate and a constant periodic payment. Present Value is the current value of the money that’s going to be received in the future with a particular rate of return.
Let’s say you will get a sum of £11,000 after a year at an annual interest rate of 7% per annum. So here, £11,000 is the Future Value(FV) that you receive after a year. Then what will be the present value of this return? Present value in this case is (11,000)/(1.07) = -£10,280.37. What this means is that if you are going to receive 11,000 in one year at 7%, then the present value of that investment is 10,280.37. So, in other words, if you invest £10,280.37 today at an interest rate of 7% per annum, you will receive £11,000 in one year.
The concept of Present Value (PV) is exactly the opposite of Future Value (FV). If you invest a principal amount (same as a present value here) for a constant period at a constant interest rate which is compounded annually, the amount you receive at the end is called Future Value.
Therefore, the formula here is:
How to use the PV function in Google Sheets?
Let’s say you are making monthly/quarterly/yearly payments instead of a lump sum payment. In that case, how would you find the Present value of the investment? Under this topic, you will learn about how to find PV of investments made in instalments.
Yearly Payment in PV function in Google Sheets
- Let’s say you are investing £5,000 every year at a fixed rate of interest of 7% per annum for 5 years.
- So here, to find the present value, use:
=PV(B1,B2,B3,B4,0)
- Since you don’t know the future value, we have kept it at 0.
- Also, since you invest the amount every year, the last parameter is set to 0.
- The output here is that the present value of the amount invested is -£20,500.99. This means that the present value of the investments made is 20,500.99.
Quarterly Payment in PV function in Google Sheets
- Let’s say you are investing £5,000 every year at a fixed rate of interest of 7% per annum for 5 years in quarters. So quarterly you will be investing a principal of (5000/4).
- So here, to find the present value, use:
=PV(B1/4,B2,B3,B4,0)
- Since you don’t know the future value, we have kept it at 0.
- Also, since you are investing the amount after every year, the last parameter is set to 0.
- The output here is that the present value of the amount invested is -£20,941.10. This means that the present value of the investments made is -£20,941.10.
Monthly Payment in PV function in Google Sheets
- Let’s say you are investing £5,000 every year at a fixed rate of interest of 7% per annum for 5 years. So monthly you will be investing a principal of (5000/12).
- So here, to find the present value, use:
=PV(B1/12,B2,B3,B4,0)
- Since you don’t know the future value, we have kept it at 0.
- Also, since you are investing the amount after every year, the last parameter is set to 0.
- The output here is that the present value of the amount invested is -£21,042.50. This means that the present value of the investments made is -£21,042.50.
How to use the PV function in Google Sheets when Payments are due BEFORE every duration?
- In this case, we will change the last parameter.
- What this means is that instead of investing the amount after the completion of the period, let’s say a year, you will be investing before the start of the year.
- This will change the PV value.
- If you see the example of Monthly payment, the PV value for an investment of (5000/12) per month for 60 months at 7% is -£21,042.50.
- However, if you invest at the start of every month, the formula of the PV function in Google Sheets will change to:
=PV(B1/12,B2,B3,B4,1)
- Here, the PV value will change to -£21,165.25.
- This means that the present value increases if you make the payments before every period, rather than after the period in this case.
Conclusion
The PV function in Google Sheets is a useful tool to calculate the present value of an investment you will receive in the future. If you want to know more about the PV function in Google Sheets, go through the documentation here.
See Also
Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.
How to View Edit History of Cells in Google Sheets | Easy 2 Minute Guide: Learn how to view the edit history of cells in Google Sheets. Learn to spot mistakes or past changes.
Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.
You can checkout other equally good articles on Google Sheets here.