Question

PROBLEM: You have decided to buy a fully loaded Audi A6 for a purchase price of $62,250. You will pay $1,000 down at the time of purchase. You will finance the balance at a nominal annual rate of 7.35 % per year to be repaid in equal monthly installments over a period of 78 months.

Using EXCEL, prepare an amortization schedule for the 78-month payoff period in the format shown in the lower box below. Make sure that all cells with a % value are formatted for percent to 2 decimals, and $ values are formatted accordingly and the values are to the nearest dollar. Use appropriate functions (PMT, IPMT, PPMT) and formulas. Save this work as Amortization in your file.

For the amortization schedule developed in 1 above, draw a stacked column graph (showing the dates along the x-axis and interest and principal payments stacked on top of one another along the y-axis. Label the graph completely. Save this as Graph1 in your file.

Name: Last, First
EDMM 3200	Summer I 2018
Excel Assignment #1
Purchase Price:
Down Payment:
Amount of Loan:
Months to pay off loan:
Annual Interest Rate:
Monthly Interest Rate:
Period Number	Payment Due Date	Monthly Payment	Interest Payment	Principal Payment	Loan Balance
0
1	4/15/18
2	5/15/18
3	6/15/18
4	7/15/18
…	…
…	…
76
77
78

Make sure you follow all the instructions given in the document “General Instructions for all Excel Assignments” posted on elearning.

Answer 1

• All working forms part of the answer
• First, the complete amortisation schedule is provided, followed by all the working including excel formula used is provided.
• Amortisation schedule in MS excel

Purchase Price (A)	$           62,250.00
Down Payment (B)	$             1,000.00
Amount of Loan ( A – B)	$           61,250.00
Months to pay off loan:	78
Annual Interest Rate:	7.35%
Monthly Interest Rate: 7.35/12	0.61%
Monthly Payment	= $ 990 [=pmt() function used]	=PMT(0.6125%,78,-61250)

AMortisation schedule is provided below:

Working for above amortisation schedule

Working with formula for above schedule:

A	B	C	D	E	F	G
209	Purchase Price:	62250
210	Down Payment:	1000
211	Amount of Loan:	=+C209-C210
212	Months to pay off loan:	78
213	Annual Interest Rate:	0.0735
214	Monthly Interest Rate:	=+C213/12
215
216	Monthly Payment	=PMT(C214,C212,-C211)
217
218	Period Number	Payment Due Date	Monthly Payment	Interest Payment	Principal Payment	Loan Balance
219	0					=+C211
220	1		=+$C$216	=IPMT($C$214,B220,$C$212,-$C$211)	=PPMT($C$214,B220,$C$212,-$C$211)	=+G219-F220
221	2		=+$C$216	=IPMT($C$214,B221,$C$212,-$C$211)	=PPMT($C$214,B221,$C$212,-$C$211)	=+G220-F221
222 - 293	3 - 74	Same as above
294	75		=+$C$216	=IPMT($C$214,B294,$C$212,-$C$211)	=PPMT($C$214,B294,$C$212,-$C$211)	=+G293-F294
295	76		=+$C$216	=IPMT($C$214,B295,$C$212,-$C$211)	=PPMT($C$214,B295,$C$212,-$C$211)	=+G294-F295
296	77		=+$C$216	=IPMT($C$214,B296,$C$212,-$C$211)	=PPMT($C$214,B296,$C$212,-$C$211)	=+G295-F296
297	78		=+$C$216	=IPMT($C$214,B297,$C$212,-$C$211)	=PPMT($C$214,B297,$C$212,-$C$211)	=+G296-F297

• Graph is provided below: