Chapter 14, Problem 1ES

To determine

To calculate: The total interest amount and future value of annuity in the following table using Excel worksheet,

Annuity payment	Annual Rate	Annual interest	Years	Type of annuity	Future value of annuity	Total interest
$1400	3%	Compounded annually	5	Ordinary

Answer to Problem 1ES

Solution:

The total interest amount and future value of annuity in the following table is,

Annuity payment	Annual Rate	Annual interest	Years	Type of annuity	Future value of annuity	Total interest
$1400	3%	Compounded annually	5	Ordinary	$7432.790	$432.790

Explanation of Solution

Given Information:

The provided table is,

Annuity payment	Annual Rate	Annual interest	Years	Type of annuity	Future value of annuity	Total interest
$1400	3%	Compounded annually	5	Ordinary

Formula used:

Sinking fund payment $\text{PMT}$ is invested in a sinking fund at an interest rate $R\%$ compounded periodically for the period $N$ then the accumulated Future value $\left(\text{FV}\right)$ is,

$FV=PMT\left(\frac{{\left(1+R\right)}^N-1}{R}\right)$

Calculation:

Consider the table,

Annuity payment	Annual Rate	Annual interest	Years	Type of annuity	Future value of annuity	Total interest
$1400	3%	Compounded annually	5	Ordinary

To find the Future value of annuity follow the steps,

Step 1: Open an Excel worksheet.

Step 2: Write the annuity payment $1400 in the cell B3.

Step 3: Write the annual rate of interest 3% in the cell B4.

Step 4: As the annual interest compounded annual hence write the number of period as 1 in the cell B5.

Step 5: Write the number of years 5 in the cell B6.

Step 6: To calculate the total number of period write “ $=\text{B6*B5}$ ” in B8.

Step 7: To calculate the periodic interest rate write “ $=\text{B4/}\left(100*\text{B}5\right)$ ” in B9.

Step 8: To calculate the Sinking fund payment write “ $=\text{B3*B9/}\left(\left(1+\text{B}9\right)^\text{B}8-1\right)$ ” in B11.

Step 9: To calculate the total payment write “ $=\text{B8*B3}$ ” in B13.

Step 10: To calculate the total interest write “ $=\text{B11}-\text{B13}$ ” in B15.

Therefore,

Therefore the completed table is,

Annuity payment	Annual Rate	Annual interest	Years	Type of annuity	Future value of annuity	Total interest
$1400	3%	Compounded annually	5	Ordinary	$7432.790	$432.790