Question

Write down the correct excel formula(s) to answer each of their questions.

A) If the Company places $1 million into an investment account earning 4% per annum, compounded quarterly and takes the legal settlement payments and invests those in the same account, will the account’s accumulated value in 5 years exceed $10 million?

B) The Company has agreed to settle a lawsuit for $11 million with a full payment in 6 years. The litigant has asked to receive the present value of the $11 million settlement, discounted at a 10% annual rate, compounded monthly. What is the correct formula?

Answer 1

A) Given information:

Investment amount (PV) = $1million

Interest rate (r) = 4% / 4 = 1% per quarter

Number of years = 5 years * 4 = 20 quarters

Calculations are:

Future value (FV) = $1,220,190.04

By using excel formula:

=FV(rate,nper,pmt,PV,type)

=FV(1%,20,0,1000000,0)

$1,220,190.04

B) Given information:

Lawsuit amount (PV) = $11 million

Number of years = 6 years

Discount rate (r) = 10% Compounded monthly

Calculations are:

Monthly payments (pmt) = $203,784.22

By using excel formula:

=pmt(rate,nper,pv,fv,type)

=pmt(10%/12,6*12,-11000000,0,0)

$203,784.22