Financial Functions in Excel

Financial functions can be carried out to calculate savings, loan value and repayments on a loan. These functions are useful as they can estimate the amount of money that an investment will produce, calculate the monthly or annual value of a loan and also the total amount that is needed to pay off a loan.

Fv (Future Value) Calculates the final value of savings

Pv (Present Value) Calculates a loan value

Pmt Calculates repayments

PMT

This function calculates the monthly cost of a loan. This is how much you will be expected to pay on a loan per month.

1. Open a workbook containing details relating to a loan with the Interest Rate, Term of loan in months and the Amount

2. Select the cell where you want to calculate Monthly Repayments. In this case select cell B5

3. On the Formulas tab in the Function Library group, select Financial

4. Select PMT

5. In the Rate text box, enter B2/12 to get the Monthly Rate. This is dividing the Interest Rate by 12 to get the Monthly Rate

6. In the Nper text box, enter B3 (Total number of payments)

7. In the Pv text box, enter B4 (The amount of the loan)

8. Click OK

9. This will calculate the monthly cost of a loan

PV

This calculates the present value of a loan. It determines what the loan is worth at this moment. It calculates this by dividing the annual rate by twelve to get the monthly rate, including the term of the loan in months and taking into account the monthly repayments.

1. Select cell B6

2. On the Formulas tab in the Function Library group, select Financial

3. Select PV

4. In the Rate text box enter B2/12 to get the monthly interest rate. This is the annual interest rate divided by 12

5. In the Nper text box type in B3. This is the term of the loan in months.

6. In the Pmt text box, type in -B5. This is the monthly cost of a loan

7. Click OK

8. This has calculated the Present Value of a loan

FV

This financial function calculates the future value of a savings account or loan amount. This is useful when trying to calculate how much the value of a savings account will be when the term of a savings period ends. It is also used to calculate the future value of a loan based on the annual interest rate, term of loan and payments made each period.

1. Select cell B7

2. On the Formulas tab in the Function Library group, select Financial

3. Select FV

4. In the Rate text box, type in B2/12. This calculation finds the monthly interest rate.

5. In the Nper text box, select B3. This includes the term of the loan in months.

6. In the Pmt text box, type in -B5. This is the monthly payment made each month

7. Click OK

8. The Future Value of the loan has been calculated. This is how much the loan will cost overall.

For more lessons on how to use formulas and functions in Excel, see the ECDL Advanced Excel book: