How to Calculate Present Value & Future Value Using Microsoft Excel. ACCT 2154 Chapter 7. Excel Functions. A function in Excel is a built-in formula. All Excel functions begin with the = sign and the function arguments (specifications) are enclosed in parentheses.
ACCT 2154 Chapter 7
You can use the PV and FV functions in Excel to determine the present value and future value of a single amount or a series of payments. Each function is used to calculate the value of a single amount,anordinary annuity or an annuity due. The function arguments specify which outcome is desired.
Since you are determining the present value of a future lump sum, the pmt is entered as 0. As this is not an annuity, the type argument may simply be omitted.
You should get an answer of $3,675.15. Don’t worry if your answer showed up as a negative.
Since this is an ordinary annuity with no lump sum amount, a comma is placed where the lump sum future value would be entered and a 0 is entered for type (0=ordinary annuity).
You should get an answer of $6,934.32. Don’t worry if your answer showed up as a negative.
For this formula, the interest rate and period must be converted because this situation requires monthly payments. The interest rate will be calculated as (.06/3 years) and the number of periods will be calculated as (3 years X 12 months). Since this is an annuity due with no lump sum amount, a comma is placed where the lump sum future value would be entered and a 1 is entered for type (1=annuity due).
The present value of an investment where 10 equal payments of $2,000 are made at the end of the each year assuming an annual interest rate of 4% is $16,221.79.
If you didn’t get the correct answer, check your formula. The function should be entered as: =PV(0.04,10,2000,,0).
Remember to enter the extra comma for the lump sum. 0 is used for the type since this is an ordinary annuity.
The present value of your prize is $10,044.96.
If you didn’t get the correct answer, check your formula. The function should be entered as: =PV(0.01,60,100,10000,1).
Since one function is used for both lump sums and annuities, you will enter all the data for this situation in one formula. However, The interest and number of periods must be converted since the $100 distribution is made monthly. Finally, the type is entered as 1 since the payment portion is an annuity due.