Microsoft Excel Assignment

Microsoft Excel Assignment

Initially Rebecca wants to borrow $15,000 over 5 years. Crown Bank will give her the loan with 8.25% interest with no loan application fee or administration charges. Bank Downunder will charge an 8% interest rate, with $150 loan application fee, and an $80 administration charge per year. State Bank will give her a special interest rate of 7.75%, but she must take the loan for 7 years. They also charge a $50 loan application fee, but no other administration fees. Which of these loans would be the cheapest?

From the excel worksheet the loan from all the three banks will have the following values:

Crown  Bank

Bank  Downunder

State  Bank

amount

15000

1/19

Microsoft Excel Assignment

15000

15000

interest  rate

8.25%

8%

7.75%

loan  application fee

0

150

50

administration  charges

2/19

Microsoft Excel Assignment

0

80

0

number of  years

5

5

7

3/19

Microsoft Excel Assignment

total amount payed after the years

1

16237.5

16280

16162.5

2

17577.09375

17662.4

4/19

Microsoft Excel Assignment

17415.0938

3

19027.20398

19155.392

18764.7635

4

20596.94831

20767.82336

20219.0327

5

22296.19655

5/19

Microsoft Excel Assignment

22509.24923

21786.0077

6

24135.63276

24389.98917

23474.4233

7

26126.82247

26421.1883

25293.6911

8

6/19

Microsoft Excel Assignment

28282.28532

28614.88336

27253.9522

9

30615.57386

30984.07403

29366.1335

10

33141.3587

33542.79996

31642.0088

7/19

Microsoft Excel Assignment

additional  charges

0

8/19

Microsoft Excel Assignment

50

For a five year period the amount that will be payable to the crown bank is 22296 dollars while for the bank down under the amount that is payable is 22509, for the state bank the amount has to be paid in seven years, therefore the cheapest loan is that from the crown bank.

Rebecca’s friends are going on holiday to Bali in July, and instead of putting all of her $5,000 savings as the deposit on the car, she is thinking of only putting down $3,000. This way she can join her friends for wild week in Bali. However she now needs to take the loan over 7 years. Crown Bank will now charge 8.5% interest, with no charges. Bank Downunder will still charge 8% and $150 loan application fee, and an $80 administration charge per year. State Bank will now charge 8% with a $50 loan application fee, but no other administration fees.

The information below provides the amount to be paid per month to each bank and also the total amount to be paid:

Crown  Bank

Bank  Downunder

State  Bank

9/19

Microsoft Excel Assignment

amount

15000

15000

15000

interest  rate

8.50%

8%

8.00%

loan  application fee

0

150

10/19

Microsoft Excel Assignment

50

administration  charges

0

80

0

number of  years

7

7

7

11/19

Microsoft Excel Assignment

total amount payed after the years

1

16275

16280

16200

2

12/19

Microsoft Excel Assignment

17658.375

17662.4

17496

3

19159.33688

19155.392

18895.68

4

20787.88051

20767.82336

20407.3344

13/19

Microsoft Excel Assignment

5

22554.85035

22509.24923

22039.92115

6

24472.01263

24389.98917

23803.11484

7

26552.13371

26421.1883

14/19

Microsoft Excel Assignment

25707.36403

8

9

10

15/19

Microsoft Excel Assignment

amount  per month for 7 years

316.0968298

314.537956

306.040048

16/19

Microsoft Excel Assignment

other  charges

150

50

17/19

Microsoft Excel Assignment

total  amount paid in seven years

26552.13371

26571.1883

25757.36403

For the seven year period the best bank to acquire the loan is the state bank whereby she will have to pay 306 dollars per month and the total amount to be paid to this bank is 25757.36403, this the lowest compared to the other two banks.

LETTER

Dear Rebecca,

Following your problem on the option to choose to acquire a loan I have made you an excel worksheet that will aid you in your decision making, you require 15000 dollars to finance your car purchase, in this case if you decide to acquire a loan from crown bank for a five year period then the amount you will be required to pay per month for five years will be 371 dollars while the total amount accumulation for this bank will be 22296 dollars.

18/19

Microsoft Excel Assignment

If you choose to acquire 15000 dollars from the bank downunder then you will be required to pay 375 dollars per month for five years, the state bank loan is offered at a much lower interest rate than the other banks but you will be required to repay the loan for seven year with a monthly installment of 301 dollars for seven years. This workings are shown in the worksheet named scenario one. However I suggest that if you are to acquire a loan from a bank using this conditions the best bank will be the crown bank because of its low installment levels.

If you wish to join your friends for the Bali holiday the amount as down payment will be low, however I assumed that you will not need to change the amount required as loan, therefore the worksheet named scenario two shows a seven year period of a loan valued 15000 dollars, according to this scenario and the conditions by the bank the crown bank offers you a chance to pay 316 dollars per month for seven years, the total amount to pay to the crown bank will be 26552 dollars, the bank downunder will require you to pay 314 dollars per month for 7 years and the total amount to be paid to this bank will be 26421 dollars plus loan application fee of 150 dollars amounting the total amount to 26571 dollars.

The state bank will offer a better loan which will require you to pay 306 dollars per month for seven years an a total amount of 25757 dollars plus application fee of 50 dollars and therefore the total amount will be 25807 dollars, this scenario is analyzed in the worksheet named scenario two. Therefore it will be better to acquire a loan from the state bank. However it would be advisable for you to sacrifice the trip in order to utilize the first option. Hope this will aid you in your decision making and in case of any extra clarification please feel free to contact me. Thank you.

Your letter may should include following:

1. the description of the problem

2. the product you have designed and how to use it

3. any special features contained within the spreadsheet which you think are worth mentioning. Perhaps you have added something to make it especially user friendly?

4. Any assumptions you have made

5. You may like to create a letterhead for your consultancy business

19/19