What Is PV in Google Sheets?
The function PV in Google Sheets or present value is used to calculate the current value of an investment or a loan. The factors taken into consideration while calculating the PV are a specified interest rate, the number of periods, and the payment amount. Thus, the present value or PV is the current value from an expected future stream of cash flow. Investors can use it to find the worth of future cash flows and determine if an investment is worthy. It can be calculated as shown in the simple examples below.
We calculate the present value in Google Sheets for a loan of $1000 at two interest rates, 5% and 7%. The number of payments is 10. We apply the formula for the PV as shown below. Since it is a cash outflow, the sign is shown as negative. The PV() function returns the present value of the investment, which indicates the amount that would have to be invested at present to achieve a desired future value or pay off the loan.
Key Takeaways
- The present value (PV) function in Google Sheets calculates the present value of an investment or loan. It is based on a constant interest rate and a series of future payments.
- The Pv in Google Sheets formula: PV(rate, number_of_periods, payment, [future_value], [end_or_beginning])
- When entering the formula, if it is not annual payments, the interest rate is divided by the number of periods. For instance, for monthly periods with a 6% annual interest rate, the rate would be 0.06/12.
- The payment argument can be entered as a negative number because it represents a cash outflow. Otherwise, the PV function’s result may be negative since it is a cash outflow.
PV() Google Sheets Formula
The syntax for the PV in Google Sheets is as follows:
PV(rate, number_of_periods, payment, [future_value], [end_or_beginning])
- rate: It is the interest rate per period. It can be any constant value or a cell reference.
- number_of_periods: The total number of payment periods of the investment or loan.
- payment: The amount paid during each period. It can be any constant value or a cell reference.
- future_value (optional): The future value that you want to obtain after the last payment is made. If omitted, it is assumed to be 0.
- end_or_beginning (optional): This value specifies whether payments are made at the end or beginning of each period. If omitted, it is assumed to be 0 (payments made at the end of every period).
NPV vs. PV in Google Sheets
While calculating the PV in Google Sheets, we often come across the term NPV.
- The PV is the current value of a future amount discounted by a specified rate of return. Individuals and companies can use it to understand the viability of an investment or expenditure.
- The NPV is the difference between the present value of cash inflows and the present value of cash outflows. Individuals do not use this but mainly large organizations to help determine the potential profitability of projects
The big difference between PV and NPV is that NPV considers the initial investment.
How to Calculate Present Value in Google Sheets?
From the Google Sheets Menu
Let us look at how to insert the function from the Google Sheets menu.
First, go to the “Insert” option. Go to “Functions” and choose “Financial.” Choose the PV function and enter the required arguments.
Manually Entering the PV function
Calculating the PV in Google Sheets involves direct use of the formula with the input values of the rate of interest and the payment period along with the amount invested.
Let us suppose you wish to end up with an amount of $25,000 in your bank account after 5 years, with a rate of interest at 5%. Find the amount you need to invest at present for this to happen.
Step 1: Enter the given details in a Google sheet.
The amount of $50,000 is entered as the future value.
Step 2: We enter the function to find the PV as follows in cell B4.
=PV(B3, B1, 0, B2, 0)
This is according to the formula =PV(rate, numberofperiods, paymentamount, [futurevalue], [endorbeginning]).
Here, as there are no intervening payments, the payment amount is taken at 0 in argument 3. The FV or future value is the value of $25,000 in cell B2.
Step 3: Press Enter. You get the present value to be invested to get a return as shown in the table above.
We use zero as the payment argument since there are no intervening payments. The present value is calculated as -$19,588.15, and as you would need to put this into your account, it shows up as a negative value.
Examples
PV in Google Sheets can be used in a number of different ways when it comes to calculations involving calculating for investments and loans. Let us look at some interesting examples below.
Example #1 – POV of Annuity
Finding the PV of an annuity involves finding the sum of the present values of all the payments received. An annuity is a financial product that provides guaranteed income over a specified period or for the rest of a person’s lifetime. Now, let us calculate the amount of an annuity of $6,000 per annum for ten years at a compound interest rate of 10% per annum.
Let us enter the details in a Google Sheet, as shown below.
Step 1: Apply the formula for PV in cell B4.
=PV(B2,B3,B1,0,0)
Step 2: Press Enter. You will get the Present value of the annuity as follows.
Example #2 – Calculating the present value of a loan
The present value of a loan can also be found using the PV function. We have a yearly interest rate of 7% and 12 payments to be made per year. The total number of payments to be made is 60 (5 years). Let the monthly payment be $1200. Calculate the loan amount you might need.
Step 1: Let us enter all the details in a Google sheet.
Step 2: Now, we have to find the loan amount to be taken with these details. Implement the formula for PV as shown below. Since we are finding the PV for a monthly payment, you have to multiply the number of years by 12 to find the total number of payments. Also, divide the annual interest rate by 12.
Similarly, if the present value involves week, month, etc, the following values are to be used:
- Semi-annual: 2
- Annual: 1
- Weekly: 52
- Quarterly: 4
- Monthly: 12.
Add the following formula in cell B5.
=PV(B1/B2, B3*B2,-B4,0,0)
The negative sign is because we will be making monthly payments as an outflow.
Step 3: Press Enter. You get an idea of the loan amount you should avail at present for these parameters.
Example #3 – Determining the value of an investment
Let us look at a PV in Google Sheets example where we find the present value of an investment that will pay $100,000 in 5 years, with an annual interest rate of 6%. We can use PV in Google Sheets to find out the amount of money we need to invest today to reach the specified amount when the investment period ends.
Step 1: Let us enter all the details in Google Sheets as shown below.
- Annual interest rate: 6%
- Number of years: 5
- Future value: 100,000
- Annuity type: 0
Step 2: Let us assume that the interest rate is compounded annually. We write the formula for the present value in B5:
=PV(B1, B2, , B3, B4)
Here, the pmt argument is omitted because it is a single lump-sum investment involving no periodic payments.
As seen below, the PV is negative, because it’s an outflow, as you are investing the money.
Example #4 – Calculating Monthly/Quarterly/Yearly Payments
Let us calculate the PV of an annuity where $250 is paid monthly with an annual interest rate of 8%. The payment is made for the next ten years. We can also calculate the quarterly/yearly payments in this case.
- Annual interest rate: 8%
- Monthly payment: 250
- Number of periods per year: 12
- Annuity type: 1
- Number of years: 10
Step 1: Now, let’s enter the formula to calculate the present value PV in Google Sheets. In this case, we omit the future value argument.
=PV(B1/B3, B3*B5, B2, , B4)
Step 2: Press Enter. You get the monthly PV result.
Quarterly
If the calculation is for quarterly instead of monthly payment, divide and multiply by four instead of 12. Alter the formula as shown below.
=PV(B1/4, 4*B5, B2, , B4)
Step 3: Press Enter. You can find the PV for quarterly payments.
Important Things To Note
- For the PV formula in Google Sheets, if the interest rate and payment amount are of different periods, we make adjustments. An important change to make the PV formula work is changing the annual interest rate to a period rate. We do this by dividing the annual rate by the number of periods per year.
- For example, if you make monthly payments, you must convert your annual interest rate to monthly by dividing by 12. We multiply the number of periods by 12.
- The present value result is usually a negative value as it is an outflow.
- If an argument is non-numeric, you get the #VALUE! Error in the PV function.
- The rate argument we use in the PV formula must be a percentage or a decimal number, such as 8% or 0.08.
Frequently Asked Questions (FAQs)
Both Present value and future value show how the value of money changes over time.
The FV function is a financial function that returns the future value of an investment, given periodic, constant payments with a constant interest rate.
Basically, money at present has greater purchasing power than the same amount in the future. Taking this into account, the FV returns the future value of an investment where you make constant and periodic payments at the same interest rate. Future value takes today’s value of money and projects its buying power at a defined time in the future.
Similarly, Present value or PV returns today’s value of a series of future payments. It also assumes periodic, constant payments at a constant interest rate. It returns the present value of an investment. Present value uses the time value of money to find what the cashflows are worth today.
Present value calculations are used in many scenarios. Whenever we purchase any asset that pays interest, be it an annuity, a bond, or real estate, it is always priced using its net present value. Stocks are also priced based on the present value. In essence, you find the PV wherever you get a chance to earn an amount now, as it is worth more than the same amount in the future due to its capacity to earn interest.
The annuity type is mentioned in the optional fifth argument of the PV function.
• For a regular annuity, use 0, where all payments are made at the end of a period. It is the default value.
• For annuity due, where all payments are made at the beginning of a period, use 1.
Download Template
This article must be helpful to understand the PV in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is PV in Google Sheets. Here we learn how to use PV in Google Sheets with examples and points to remember. You can learn more from the following articles.
Leave a Reply