Question

Tables Charts SmartArt Format Data Rei . - Ariel 12AA- 12 A abe W rap Text General Paste Clear BI U S A EI Merge - 9 % 0414B * ef pay-off ABT C D F 1 Homework 5: Mortgage Calculation ($100,000 Loan Amount at 6% for 30 years) G H I No. Balance Interest =PMT(6%/12,360,-100000) Payment Pay-off Mo JAN 2 FEB MAR APR 5 MAY 6 JUN 7 JUL 8 AUG 9 SEP 10 OCT 11 NOV 12 DEC Year 1 Total $0.00 $0.00 17 $0.00 (The following is for Year 12:) No. Mo Balance Interest 133 JAN $81,451.48 $407.26 134 FEB $81,259.19 $406.30 135 MAR $81,065.94 $405.33 136 APR $80.871.72 $404.36 144 Sheetl Sheet2 Sheet3 + Normal View Ready Payment $599.55 $599.55 $599.55 $599.55 Pay-off $192.29 $193.25 $194.22 $195.19 LEA 986 BOWPXOCO MacBook Pro

Answer 1

в -100000 0.06 57 93 A 1 Loan Amount 2 Interest Rate 3 Period (Yrs) 4 No. of payments per year Formuale 6 No Month 71 Jan 82 Feb Mar 10 4 Apr May 126 Jun 137 Jul 14 8 Aug 15 9 Sep 16 10 Oct 17 11 Nov 18 12 Dec 19 Year 1 Total 20 21 11 5 Balance Interest Payment Pay-off --C1-F7 =IPMT($C$2/12,A7,$C$3*$C$4,$C$1) =PMT($C$2/12,$C$3*$C$4,$C$1) =PPMT($C$2/12, A7,$C$3*$C$4,$C$1) =C7-F7 IPMT($C$2/12, A8,360, $C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A8,360,$C$1) =C8-F8 =IPMT($C$2/12, A9,360,$C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A9,360,$C$1) =C9-F9 I PMT($C$2/12, A10,360, $C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A10,360, $C$1) =C10-F10 =IPMT($C$2/12, A11,360, $C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A11,360, $C$1) =C11-F11 =IPMT($C$2/12, A12,360, $C$1) =PMT($C$2/12,360,$C$1) =PPMT($C$2/12, A12,360, $C$1) =C12-F12 =IPMT($C$2/12, A13,360,$C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A13,360,$C$1) =C13-F13 =IPMT($C$2/12, A14,360, $C$1) =PMT($C$2/12,360,$C$1) =PPMT($C$2/12, A14,360, $C$1) =C14-F14 =IPMT($C$2/12, A15,360,$C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A15,360, $C$1) =C15-F15 FIPMT($C$2/12, A16,360, $C$1) =PMT($C$2/12,360, $C$1) =PPMT($C$2/12, A16,360, $C$1) =C16-F16 =IPMT($C$2/12, A17,360,$C$1) =PMT($C$2/12,360,$C$1) =PPMT($C$2/12, A17,360,$C$1) =C17-F17 =IPMT($C$2/12, A18,360,$C$1) =PMT($C$2/12,360,$C$1) =PPMT($C$2/12, A18,360,$C$1) =SUM(D7:D18) =SUM(E7:E18) =SUM(F7:F18)

Loan Amount		-100000
Interest Rate		6%
Period (Yrs)		30
No. of payments per year		12
Answer :
No	Month	Balance	Interest	Payment	Pay-off
1	Jan	$99,900.45	$500.00	$599.55	$99.55
2	Feb	$99,800.90	$499.50	$599.55	$100.05
3	Mar	$99,700.85	$499.00	$599.55	$100.55
4	Apr	$99,600.30	$498.50	$599.55	$101.05
5	May	$99,499.25	$497.99	$599.55	$101.56
6	Jun	$99,397.69	$497.49	$599.55	$102.06
7	Jul	$99,295.63	$496.98	$599.55	$102.57
8	Aug	$99,193.06	$496.46	$599.55	$103.09
9	Sep	$99,089.97	$495.95	$599.55	$103.60
10	Oct	$98,986.37	$495.43	$599.55	$104.12
11	Nov	$98,882.24	$494.91	$599.55	$104.64
12	Dec	$98,777.60	$494.39	$599.55	$105.16
Year 1	Total		$5,966.59	$7,194.61	$1,228.01