Scenario $($A$)$

Les and Audrey $($name changes protect the innocent$)$ are happily married, and are both in business, Les running an accordion factory and Audrey operating a millinery

service. They are keen that their businesses do not take over at home; in particular, Audrey has a deep dislike of everything to do with accordions. Some years ago,

on the first of January, their financial adviser Andy recommended that they each should follow a three$-$year investment plan, with a view to obtaining and fitting$-$out

suitable business premises. Les immediately started investing $f1100$ per month at an annual equivalent interest rate of $5\mathrm{.}5\%$ pa$.$ At the same time he also invested a

lump sum of $f18,000$ at $6\mathrm{.}1\%$ pa$.$ Audrey decided to start at the end of the month, making a regular monthly investment at $0\mathrm{.}38\%$ per month. Andy, knowing that

Audrey would require something in the region of $40,000,$ was able to advise her of a suitable amount to invest.

Questions $($A$)$

$[1]$ Find the monthly rate of interest equivalent to Les's quoted AER of $5\mathrm{.}5\%.$

$[2]$ What is the AER of Audrey's investment plan?

$[3]$ To the nearest pound how much will Les have accumulated at the end of three years?

$[4]$ To the nearest pound, how much is Audrey's monthly investment?

Scenario $($B$)$

After three years, Les and Audrey each find suitable separate business premises to buy and refurbish, and they each make down payments of $62,000.$

In addition, they each borrow $f190,000$ by taking out secured loans over a $25-$year period at an APR of $8\mathrm{.}1\%.$

Questions $($B$)$

$[5]$ What is the monthly rate of interest equivalent to an APR of $8\mathrm{.}1\%?$

$[6]$ To the nearest pound what are the monthly repayments?

$[7]$ How much would be outstanding on each loan after ten years of repayments, assuming nothing changes?

Scenario $($C$)$

After ten years, interest rates rise to a monthly rate of $0\mathrm{.}68\%.$

Questions $($C$)$

$[8]$ Les, who dedicates his life to accordions, decides to increase his monthly repayments so that the repayment period for his loan does not

increase. What are his new monthly repayments?

$[9]$ Audrey, who prefers to spend her time and money on family, works part$-$time and draws heavily from the business. She cannot afford to

increase her monthly repayments, so opts to extend the period she will be repaying. For how many months will the repayment period be

extended?

$[10]$ Audrey is surprised when she wins a national competition for her talented work. Andy recommends that she invests most of her prize money

until she is more certain of the direction she would like to take the business. Find the NPV $($at $3\mathrm{.}8\%pa)$ and the IRR for an investment now of

f$20,000$ with the following cash flows. Would you recommend going ahead with this investment? Explain your recommendation.

Requirements

$($i$)$ Using a blank MS Excel Worksheet, calculate answers to the above questions, ensuring that each worked answer is clearly identified with the

question number.

can you show excel forumla