Advanced Spreadsheets – Financial Functions

Use financial functions: fv, pv, pmt.

In the same wizard, under the ‘Financial’ category, we have the ‘FV’ function. This function returns the future value of an investment based on regular payments and a constant rate of interest.

This function takes up five values, ‘Rate’, ‘NPER’, ‘PMT’, ‘PV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘PMT’ is the regular payments, the constant annuity to be paid in each period. ‘PV’ is the present value, the current value of a series of payments.

Let us take an example for the ‘FV’ function. Say the annual rate of interest, ‘Rate’, is ‘0.06’. The number of payments, ‘NPER’, is ‘10’. The amount of regular payments, ‘PMT’, is ‘-200’. The present value, ‘PV’, is ‘-500’. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end. Here we will take the ‘Type’ as 1. So in this case the payments are due at the start of each period.

After providing all the inputs, when we click on ‘OK’, the value returned by the ‘FY’ function will be ‘3689.75’, as has been shown in the illustration. This value is achieved by using the appropriate financial calculation methodology. This is how the ‘FV’ function works.

Similarly, we have the ‘PV’ function. This function calculates the present value of an investment. This function takes up similar five arguments, ‘Rate’, ‘NPER’, ‘PMT’, ‘FV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘PMT’ is the regular payments, the constant annuity to be paid in each period. ‘FV’ is the future value, the final value to be attained after the last payment. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end.

We have already seen an example for the ‘FV’ function. The ‘PV’ function works in a similar manner. The only difference is that here, instead of ‘PV’, we have ‘FV’ as one of the arguments.

We also have a ‘PMT’ function. This function returns the periodic payment of an annuity, based on regular payments and a fixed periodic interest rate. This function takes up similar five arguments, ‘Rate’, ‘NPER’, ‘FV’, ‘PV’ and ‘Type’. ‘Rate’ is the rate of interest per period. ‘NPER’ is the payment period, the total number of periods in which the annuity is paid. ‘FV’ is the future value, the final value to be attained after the last payment. ‘PV’ is the present value, the current value of a series of payments. ‘Type’ defines whether the payment for each period is due at the beginning of the period or at the end of the period. ‘1’ denotes due at the beginning and ‘0’ denotes due at the end.

We have already seen an example for the ‘FV’ function. The ‘PMT’ function works in a similar manner. The only difference is that here, instead of ‘PMT’, we have ‘FV’ as one of the arguments.