Question

using excel functions

Complete the following without a table (round each calculation to the nearest cent as

needed):

1. principal time rate of compounding interest   compounded NUMBER of periods compounded total amount Total interest

$ 500   1 year 8% quarterly A. ? B. ? C.?

2. Solve the previous problem by using compound value (FV). See Table 12.1.

3. Lionel Rodgers deposits $7,000 in Victory Bank, which pays 4% interest compounded

   semiannually. How much will Lionel have in his account at the end of 8 years?

4. Find the effective rate (APY) for the year: principal, $8,000; interest rate, 6%; and com-

   pounded quarterly. Round to the nearest hundredth percent.

5. Calculate by Table 12.2 what $1,800 compounded daily for 5 years will grow to at 6%.

Answer 1

Since the question are to be solved using excel functions, we can directly get the values using in-built functions in excel.

1.)  

Principal	Time	Rate of compound interest	Compounded	Number of periods compounded	Total amount	Total interest
$500	8 year	8%	Quarterly	?	?	?

Number of periods compounded = 8*4 = 32 ( This is because there is quarterly compounding ie. 4 times a year compounding and there are total 8 years)

Total amount can be calculated using FV function in excel

rate = 0.08/4 ( Since 0.08 is the annual interest, 0.08/4 is the quarterly rate of interest)

nper = 32 ( Number of periods compounded)

pmt = 0 ( since there are no intermediate cash-flows)

pv = -500 ( since the amount is deposited today)

we get, FV = $942.27

Total interest = Future value or the total amount - The principal

Total interest = 942.27-500 = $442.27

2)

The compound value (FV) is the same as calculated for Total amount in part 1)

Hence compound value (FV) = $942.27

3)

We use FV function in excel to get how much will Lionel have in his account at the end of 8 years

rate = 0.04/2 ( Since 0.04 is the annual interest, 0.04/2 is the semi-annual rate of interest)

nper = 16 ( Number of periods compounded = 8*2)

pmt = 0 ( since there are no intermediate cash-flows)

pv = -7000( since the amount is deposited today)

we get, FV = $9609.5

Lionel will have $9609.5 in his account at the end of 8 years

4)

The effective rate for the year is given by:

6% is the annual interest rate compounded quarterly

Hence 0.06/4 = 0.015 is the quarterly rate of interest.

To get an effective annual rate, we compound the quarterly rate of interest 4 times

Hence, Effective rate (APY) = ((1+0.015)^4) -1 = 0.06136

Effective rate (APY) =6.136%

5)

We use FV function in excel to get the required amount

rate = 0.06/365 ( Since 0.06 is the annual interest, 0.06/365 is the semi-annual rate of interest)

nper = 1825 ( Number of periods compounded = 5*365)

pmt = 0 ( since there are no intermediate cash-flows)

pv = -1800 ( since the amount is deposited today)

we get, FV = $2429.69