Saturday, 27 October 2018

PV Finance Function in Google Sheet

PV Function or Present Value Function is a Finance Function in the Google sheet. It used to calculate the present value of an investment or loan based on a fixed periodic payment and a  constant interest rate.

PV Finance Function


In finance, The Present Value is also known as Discounted Value also. Calculation of PV helps us to evaluate the reasonableness of any future financial benefits or its obligation.


It also uses in the financial statement analysis where the present value may calculate the present value a series of future payments based on periodic fixed payments and interest rate.

Syntax



PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])


Compulsory Arguments


Rate: It means the Interest Rate for the period.
Number of Periods: Total number of the payment period
Payment Amount: The amount to be paid in each period

Optional Arguments


Future Value: It represents the future value after paying the final payment.
End or Beginning: It may be 0 or 1. When the payments are due at the end, we need to use 0. If the payment is due at the beginning, we need to use 1. By default, it will be 0.


Points to Remember

The Rate, Number of Periods and the Payment must be in a consistent unit. 

In case a loan is for 24 months and all the loan payment may be monthly. In this case, All the above said parameters like rate, number of period and payment must be used as the monthly figure in the function.

Apply the PV Finance function in Google sheet 

Let us consider some examples to have a clear idea about the PV function.

Example 1


PV Finance Function

In the above table, we have some data for case 1 and case 2

Case 1: We have Interest rate 5%, Number of payments 36 months, Payment amount 1000. FV and the type of payment are 0. 
There we have applied the formula as

=PV(C3/12,C4,C5,C6,C7)

Here we can see that the interest rate is annual and all other parameters are on a monthly basis.

So in our equation, we have converted the annual rate as a monthly rate. C3/12 is indicating it and the result is -$ 33,241.07.

Case 2: Here also we are considering the same data but with an additional value for End or Beginning. The result is -$33,504.73

There also we used the same formula, but we got a different answer because of the change in the payment type that whether the payment is due at the end or at the beginning. 

Here it is at the beginning.

Example 2 


PV Finance Function


In this table, we can see the data and payments on a weekly basis.

Case 1: It provided an interest rate of 5%, the number of payments is 156 weeks, payment per week is 250.

Here we are using the equation as 


=PV(H3/52,H4,H5,H6,H7)

As the interest is given in annualized rate, we used H3/52 to convert the rate into the weeks. The result is -$36,199.80

Case 2: Here we have the same data except for the End or Beginning value. 

In Case 2, the payment is due at the beginning, then the value used is 1 and the result is $36,234.61.

Points to Remember

We need to always remember to convert the annual interest rate into the appropriate rate as per the question like monthly, quarterly, weekly etc. This is a common mistake observed in the use of PV finance function in Google sheet.

We need to provide the numeric value for all the arguments in the formula, otherwise, it may result in a #VALUE! error.


Hope you understand the PV Finance function and its application in Google sheet.
Qatar Airways