Financial Formulas and Functions on Microsoft Excel for Present Value

Previously we talked about the future value which is one of the two main types of time value of money. Accordingly, we should continue time value of money to know more about the second type i.e. Present Value called PV for this article.
The point of this article is showing us the calculations of the various financial formulas of PV with Microsoft Excel. Each financial formula and its own pattern and details come along with the calculation of the given values of all elements from the formula in addition to the PV function on Microsoft Excel. As the result, we can choose the right one to match our criteria and know how to exercise it to meet our requirements. To ensure the results of the following pictures and files relevant to the financial formulas mentioned above, the online calculator as below can work with the pictures or files.

1) PV of a Future Sum: Given FV= 300.73 the only one future sum, r= 6% interest, n= 7 the number of years.

Fill in function arguments of PV as follows: Rate= 6%, Nper= 7, FV= -300.73, Type= 0 (end= ordinary) or leave it blank

2) PV of a Future Sum with Compounding: the same as above with the additional compounding per year= 12.

Fill in function arguments of PV as follows: Rate= 6%, Nper= 7*12, FV= -300.73, Type= 0 (end= ordinary) or leave it blank

3) PV of an Annuity: Given PMT= 200 once a year, interest= 4%, n= 10 the number of years. (The basic of PV calculation is partly detailed for ensuring the accuracy.)

Fill in function arguments of PV as follows: Rate= 4%, Nper= 10, PMT= -200, Type= 0 (end= ordinary) or leave it blank, Type= 1 (beginning= due)

4) PV of a Growing Annuity (g ≠ i): Given PMT= 200 once a year, g (growth per PMT) = 4% & 7% for two examples, interest= 6%, n= 2 the number of years

Fill in function arguments of NPV instead of PV and prepare cash flow column from the first payment to the last one with annual growth rate of 4% for the first example and the growth rate of 7% for the second one as follows: Rate= 6%, value1= range of cash flow= B5:B6 for example 1 and B9:B10 for example 2

5) PV of a Growing Annuity (g = i): Given PMT= 200 once a year, g= 6%, interest= 6%, n= 3 the number of years

Fill in function arguments of NPV instead of PV and prepare cash flow column from the first payment to the last one with annual growth rate of 6% for the value 1 in the arguments and rate= interest 6%

6) PV of Combined FV andCash Flow with Compouding (m=q): Given Single Sum of FV= 15,000 and PMT= 1,000 once a year, interest= 6%, n= 10 the number of years, m= 3 compounding per period (year) and m= q (payment frequency per period)

Fill in function arguments of PV as follows: Rate= 6%/3, Nper= 10*3, PMT= -1000, FV= -15000, Type= 0 (end= ordinary) or leave it blank, Type= 1 (beginning= due)

Effect (function): effective interest rate= (6%, 3) = (1+r/m)^m-1 in the financial formula

7) PV of Combined FV and CashFlow with Compounding (m≠q):  Given Single sum of FV= 15000 and PMT= 1000 once a year, interest= 6%, n= 10 the number of the years, m= 12 compounding per period (year), q= 3 payment frequency per period

When compounding (m) is not equal to the payment frequency, we have to make the original interest rate to coincide with payment frequency (q) and use the new interest rate in the financial formula and Microsoft Excel’s function. The new interest rate is 6.0452%.

Fill in function arguments of PV as follows: Rate= 6.0452%/3, Nper= 10*3, PMT= -1000, FV= -15000, Type= 0 (end= ordinary) or leave it blank, Type= 1 (beginning= due)

Effect (function): effective interest rate= (6.0452%, 3) = (1+r/q)^q-1 in the financial formula

So far, those detailed demonstrations have told us how many types of PV there are but there are some on my hand have not yet been talked. Moreover, I have herewith supplied all of you my link of spreadsheets above for looking into the working formulas and functions inside Microsoft Excel.

On the next post, I will give you the remaining formulas which include the uneven cash flow, continuous compounding and perpetuity. There are the new forms and new functions for learning apart from the new items.

Additionally, we can follow the link of Calculator Soup below to study more about the present value and its elements’ definition including its online calculator. You can find it very useful, for it has its online calculator, the derivations of formulas and the effective rate from Microsoft Excel’s Functions. http://www.calculatorsoup.com/calculators/financial/present-value-calculator.php

Please enjoy your reading.