Question

Please answer all the questions posted. the 2 pictures in this case. thank you very much

you mean this format? please let me know if this is it

Answer 1

Question 1:

Amount having Purchasing power equivalent to $40,000 today = $40,000 * Annuity of 3% for 10 years

= $40,000 * 1.343916

= $53,756.64

Present value of amount to be paid as retirement benefit after 10 years = $53,756.64 * (1 + Cumulative Discounting factor of 9% for 24 years)

= $53,756.64 * (1 + 9.706612)

= $575,551.59

Present value of retirement benefits today = $575,551.59 * Discounting factor of 9% for 10 years

= $575,551.59 * 0.422411

= $243,119.32

This implies that to enjoy the retirement benefits, the savings should have a present value of $243,119.32. Current savings today are $75,000. Therefore, the annual savings should have a present value of $168,119.32.

Let annual savings be X

$168,119.32 = X * Cumulative discounting factor of 9% for 10 years

$168,119.32 = X * 6.417658

X = $168,119.32 / 6.417658

X = $26,196.37

Annual Savings at the end of the year are $26,196.37

Notes:

1. All the discounting factors are rounded off to 6 decimal places.
2. All $ amounts are rounded off to 2 decimal places

Question 2:

Loan Amortization $ 30,00,000.00 Loan Amount Term in years Annual Coupon Rate 16% Calculation of Loan payment Loan Payment = $ 9,16,228.14 (=PMT(B5,B4,-B3)) Loan Amortization Schedule Year Begg. Bal Payment Interest Principal Ending Bal 1 $ 30,00,000.00 $ 9,16,228.14$ 4,80,000.00 $ 4,36,228.14$ 25,63,771.86 2 $ 25,63,771.86 $ 9,16,228.14$ 4,10,203.50 $ 5,06,024.65 $ 20,57,747.21 $ 20,57,747.21 $ 9,16,228.14 $ 3,29,239.55 $ 5,86,988.59$ 14,70,758.62 $ 14,70,758.62 $ 9,16,228.14 $ 2,35,321.38 $ 6,80,906.77 $ 7,89,851.85 5 $ 7,89,851.85 $ 9,16,228.14 $ 1,26,376.30 $ 7,89,851.85 $ - Payment (=B8) Formulas Loan Amortization Schedule Year Begg. Bal 1 (=B3) 2 (=F12) (=F13) (=F14) 5 (=F14) (=B8) Interest (=B12*B5) (=B13*B5) (=B14* B5) (=B15*B5) (=B16* B5) Principal (=C12-D12) (=C13-D13) (=C14-014) (=C15-D15) (=C16-D16) Ending Bal (=B12-D12) (=B13-D13) (=B14-D14) (=B15-D15) (=B16-D16) 4 (=B8) (=B8) (=B8) Percentage of 2nd year payment representing repayment of principal 55.23% (=E 13/C13)

Notes:

1. Be careful while reading amounts (with commas)
2. The formulas are entered in brackets.
3. Cell references are used as per your question. So you can copy the answer as it is.