Question

Simone is now 50 years old and plan to retire at age 67 (in 17 years). She currently has a share portfolio worth $750,000, a superannuation fund worth $1,200,000, and a money market (similar to cash) account worth $500,000. Her share portfolio is expected to provide annual returns of 12% p.a. (compounded annually), her superannuation will earn her 9% p.a. (compounded annually), and the money market account earns 1.2% p.a. (compounded monthly). Assume all these returns are aftertax. Assume Simone’s superannuation contribution is $25,000 (after-tax) per year for the next 17 years (starting 1 year from now).

c. Simone is looking to purchase a Mercedes Benz SL500, which has a total cost of $350,000. To maintain sufficient liquidity, Simone plans to withdraw $150,000 from her money market account and will pay the rest by taking on a 6.6% p.a. (compounded monthly) 5-year bank loan. What is the monthly payment on this car loan if the first instalment is lodged in one month? Prepare an amortization table using Excel.

Need complete formula and calculation process.

Answer 1

Total cost	$3,50,000
-Withdraw from money market account	-$1,50,000
Loan Amount	$2,00,000
Monthly Payment
=PMT(6.6%/12,60,200000,0,0)
$3,923

Month	Opening	Interest (Opening*6.6%/12)	Installment	Payment of Principal (Installment-Interest)	Closing (Opening-Payment of Principal)
1	$2,00,000	$1,100	$3,923	$2,823	$1,97,177
2	$1,97,177	$1,084.48	$3,923	$2,838.13	$1,94,339.27
3	$1,94,339	$1,068.87	$3,923	$2,853.74	$1,91,485.53
4	$1,91,486	$1,053.17	$3,923	$2,869.43	$1,88,616.09
5	$1,88,616	$1,037.39	$3,923	$2,885.22	$1,85,730.88
6	$1,85,731	$1,021.52	$3,923	$2,901.08	$1,82,829.79
7	$1,82,830	$1,005.56	$3,923	$2,917.04	$1,79,912.75
8	$1,79,913	$989.52	$3,923	$2,933.08	$1,76,979.67
9	$1,76,980	$973.39	$3,923	$2,949.22	$1,74,030.45
10	$1,74,030	$957.17	$3,923	$2,965.44	$1,71,065.02
11	$1,71,065	$940.86	$3,923	$2,981.75	$1,68,083.27
12	$1,68,083	$924.46	$3,923	$2,998.15	$1,65,085.12
13	$1,65,085	$907.97	$3,923	$3,014.64	$1,62,070.49
14	$1,62,070	$891.39	$3,923	$3,031.22	$1,59,039.27
15	$1,59,039	$874.72	$3,923	$3,047.89	$1,55,991.38
16	$1,55,991	$857.95	$3,923	$3,064.65	$1,52,926.73
17	$1,52,927	$841.10	$3,923	$3,081.51	$1,49,845.22
18	$1,49,845	$824.15	$3,923	$3,098.46	$1,46,746.77
19	$1,46,747	$807.11	$3,923	$3,115.50	$1,43,631.27
20	$1,43,631	$789.97	$3,923	$3,132.63	$1,40,498.64
21	$1,40,499	$772.74	$3,923	$3,149.86	$1,37,348.77
22	$1,37,349	$755.42	$3,923	$3,167.19	$1,34,181.59
23	$1,34,182	$738.00	$3,923	$3,184.61	$1,30,996.98
24	$1,30,997	$720.48	$3,923	$3,202.12	$1,27,794.86
25	$1,27,795	$702.87	$3,923	$3,219.73	$1,24,575.13
26	$1,24,575	$685.16	$3,923	$3,237.44	$1,21,337.69
27	$1,21,338	$667.36	$3,923	$3,255.25	$1,18,082.44
28	$1,18,082	$649.45	$3,923	$3,273.15	$1,14,809.29
29	$1,14,809	$631.45	$3,923	$3,291.15	$1,11,518.14
30	$1,11,518	$613.35	$3,923	$3,309.25	$1,08,208.88
31	$1,08,209	$595.15	$3,923	$3,327.46	$1,04,881.43
32	$1,04,881	$576.85	$3,923	$3,345.76	$1,01,535.67
33	$1,01,536	$558.45	$3,923	$3,364.16	$98,171.51
34	$98,172	$539.94	$3,923	$3,382.66	$94,788.85
35	$94,789	$521.34	$3,923	$3,401.27	$91,387.58
36	$91,388	$502.63	$3,923	$3,419.97	$87,967.61
37	$87,968	$483.82	$3,923	$3,438.78	$84,528.83
38	$84,529	$464.91	$3,923	$3,457.70	$81,071.13
39	$81,071	$445.89	$3,923	$3,476.71	$77,594.42
40	$77,594	$426.77	$3,923	$3,495.84	$74,098.59
41	$74,099	$407.54	$3,923	$3,515.06	$70,583.52
42	$70,584	$388.21	$3,923	$3,534.40	$67,049.13
43	$67,049	$368.77	$3,923	$3,553.83	$63,495.29
44	$63,495	$349.22	$3,923	$3,573.38	$59,921.91
45	$59,922	$329.57	$3,923	$3,593.03	$56,328.88
46	$56,329	$309.81	$3,923	$3,612.80	$52,716.08
47	$52,716	$289.94	$3,923	$3,632.67	$49,083.42
48	$49,083	$269.96	$3,923	$3,652.65	$45,430.77
49	$45,431	$249.87	$3,923	$3,672.74	$41,758.04
50	$41,758	$229.67	$3,923	$3,692.94	$38,065.10
51	$38,065	$209.36	$3,923	$3,713.25	$34,351.86
52	$34,352	$188.94	$3,923	$3,733.67	$30,618.19
53	$30,618	$168.40	$3,923	$3,754.20	$26,863.98
54	$26,864	$147.75	$3,923	$3,774.85	$23,089.13
55	$23,089	$126.99	$3,923	$3,795.61	$19,293.52
56	$19,294	$106.11	$3,923	$3,816.49	$15,477.02
57	$15,477	$85.12	$3,923	$3,837.48	$11,639.54
58	$11,640	$64.02	$3,923	$3,858.59	$7,780.96
59	$7,781	$42.80	$3,923	$3,879.81	$3,901.15
60	$3,901	$21.46	$3,923	$3,901.15	$0.00