Question

Problem #1 (5 points)

Recently you purchased a nice car for $52,000 on January 1, 20x1.  

The dealer asked you to $2000 down and offered you financing for the remaining. The condition of the financing was as follows:

1. Total period was 7 years.

2. Interest rate is 1.9%

3. Your payment should be at the end of each month.

Required to calculate your monthly payments using the appropriate time value of money provided for you at attached documents and also posted on the blackboard. You must prepare the following the schedule in Excel and fill in the numbers. Make sure DO NOT type the numbers and rather transfer the cells.

Schedule of Loan Amortization

#of Period in years ?

# of period in months ?

Interest rate per year ?

Interest rate per month ?

Period Payments Interest Principle Balance

1/1/X1 ----- ------ ------ $50,000

1/30/X1

    .

    .  

    .

    .  

Problem #2 (10 points)

Lincoln Corporation Issued $10,000,000 with the following codition:

Period of the bond 30 years.

Stated interest rate is 10%

Effective (market) rate is 12%

Interest is paid at the end of each year.

Calculate price of the bond using appropriate present value formula available to you and then prepare the following schedule on an Excel spreadsheet.

Schedule of Bond Amortization

Period Payments Interest Principle Book Value

1 ----- ------ ------ $ ?

2

    .

    .  

    .

    .

30  

Answer 1

Problem #1

#of Period in years	7
# of period in months	84
Interest rate per year	1.90%
Interest rate per month	0.16%

Period Payment Interest Principle Balance 1 $ 636.17 $ 79.17 $ 557.00 $ 49,443.00 2 $ 636.17 $ 78.28 $ 557.88 $ 48,885.11 3 $ 636.17 $77.40 $ 558.77 $ 48,326.35 4 $ 636.17 $ 76.52 $ 559.65 $ 47,766.69 5 $ 636.17 $ 75.63 $ 560.54 $ 47,206.16 6 $ 636.17 $ 74.74 $ 561.43 $ 46,644.73 7 $ 636.17 $ 73.85 $ 562.31 $ 46,082.41 8 $ 636.17 $ 72.96 $ 563.21 $ 45,519.21 9 $ 636.17 $ 72.07 $ 564.10 $ 44,955.11 10 $ 636.17 $ 71.18 $ 564.99 $ 44,390.12 11 $ 636.17 $ 70.28 $ 565.88 $ 43,824.24 12 $ 636.17 $ 69.39 $ 566.78 $ 43,257.46 13 $ 636.17 $ 68.49 $ 567.68 $ 42,689.78 14 $ 636.17 $ 67.59 $ 568.58 $ 42,121.20 15/$ 636.17 $66.69 $ 569.48 $ 41,551.72 16 $ 636.17 $ 65.79 $ 570.38 $ 40,981.35 17 $ 636.17 $64.89 $ 571.28 $ 40,410.06 18 $ 636.17 $ 63.98 $ 572.19 $ 39,837.88 19$ 636.17 $ 63.08 $ 573.09 $ 39,264.79 20 $ 636.17 $ 62.17 $ 574.00 $ 38,690.79 21 $ 636.17 $ 61.26 $ 574.91 $ 38,115.88 22 $ 636.17 $ 60.35 $ 575.82 $ 37,540.06 23 $ 636.17 $59.44 $ 576.73 $ 36,963.33 24 $ 636.17 $ 58.53 $ 577.64 $ 36,385.68 25 $ 636.17 $ 57.61 $ 578.56 $ 35,807.13 26 $ 636.17 $ 56.69 $ 579.47 $ 35,227.65 27 $ 636.17 $ 55.78 $ 580.39 $ 34,647.26 28 $ 636.17 $ 54.86 $ 581.31 $ 34,065.95 29 $ 636.17 $ 53.94 $ 582.23 $ 33,483.72 30 $ 636.17 $ 53.02 $ 583.15 $ 32,900.56 31 $ 636.17 $ 52.09 $ 584.08 $ 32,316.49 32 $ 636.17 $ 51.17 $ 585.00$ 31,731.49 33 $ 636.17 $ 50.24 $ 585.93 $ 31,145.56 34 $ 636.17 $ 49.31 $ 586.86 $ 30,558.70 35 $ 636.17 $ 48.38 $ 587.78 $ 29,970.92 36 $ 636.17 $ 47.45 $ 588.72 $ 29,382.20 37 $ 636.17 $ 46.52 $ 589.65 $ 28,792.56 38 $ 636.17 $ 45.59 $590.58 $ 28,201.98 39 $ 636.17 $ 44.65 $ 591.52 $ 27,610.46 40 $ 636.17 $ 43.72 $ 592.45 $ 27,018.01 41 $ 636.17 $ 42.78 $ 593.39 $ 26,424.62 42 $ 636.17 $ 41.84 $ 594.33 $ 25,830.29 43 $ 636.17 $ 40.90 $ 595.27 $ 25,235.02 44 $ 636.17 $39.96 $ 596.21 $ 24,638.80 45 $ 636.17 $39.01 $ 597.16 $ 24,041.64 46 $ 636.17 $ 38.07 $ 598.10 $ 23,443.54 47 $ 636.17 $ 37.12 $ 599.05 $ 22,844.49 48 $ 636.17 $ 36.17 $ 600.00 $ 22,244.49 49 $ 636.17 $ 35.22 $ 600.95 $ 21,643.54 50 $636.17 $ 34.27 $ 601.90 $ 21,041.64 51 $ 636.17 $ 33.32 $ 602.85 $ 20,438.79 52 $ 636.17 $ 32.36 $ 603.81 $ 19,834.98 53 $ 636.17 $ 31.41 $ 604.76 $ 19,230.22 54 $ 636.17 $ 30.45 $ 605.72 $ 18,624.50 55 $ 636.17 $ 29.49 $ 606.68 $ 18,017.82 56 $ 636.17 $ 28.53 $ 607.64 $ 17,410.18 57 $ 636.17 $ 27.57 $ 608.60 $ 16,801.58 58 $ 636.17 $ 26.60 $ 609.57 $ 16,192.01 59 $ 636.17 $ 25.64 $ 610.53 $ 15,581.48 60 $ 636.17 $ 24.67 $ 611.50 $ 14,969.98 61 $ 636.17 $ 23.70 $ 612.47 $ 14,357.51 62 $ 636.17 $ 22.73 $ 613.44 $ 13,744.08 63 $ 636.17 $ 21.76 $ 614.41 $ 13,129.67 64 $ 636.17 $ 20.79 $ 615.38 $ 12,514.29 65 $ 636.17 $ 19.81 $ 616.35 $ 11,897.93 66 $ 636.17 $ 18.84 $ 617.33 $ 11,280.60 67 $ 636.17 $ 17.86 $ 618.31 $ 10,662.29 68 $ 636.17 $ 16.88 $ 619.29 $ 10,043.01 69 $ 636.17 $ 15.90 $ 620.27 $ 9,422.74 70 $ 636.17 $ 14.92 $ 621.25 $ 8,801.49 71 $ 636.17 $ 13.94 $ 622.23 $ 8,179.26 72 $ 636.17 $12.95 $ 623.22 $ 7,556.04 73 $ 636.17 $ 11.96 $ 624.21 $ 6,931.83 74 $ 636.17 $ 10.98 6625.19 $ 6,306.64 75$ 636.17 $9.99 $ 626.18 $ 5,680.46 76 $ 636.17 $ 8.99$ 627.17 $ 5,053.28 77 $ 636.17 $ 8.00 $ 628.17 $ 4,425.11 78 $ 636.17 $ 7.01 $ 629.16 $ 3,795.95 79$ 636.17 $ 6.01 $ 630.16 $ 3,165.79 80 $ 636.17 $ 5.01 $ 631.16 $ 2,534.64 81 $ 636.17 $ 4.01 $ 632.16 $ 1,902.48 82 $ 636.17 $ 3.01 $ 633.16 $ 1,269.32 83 $ 636.17 $ 2.01 $ 634.16 $ 635.16 84 $ 636.17 $ 1.01 $ 635.16 $ -0.00

A E F 1 4 5 6 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 B C D #of Period in years 7 # of period in months =C1*12 Interest rate per year 0.019 Interest rate per month =C3/12 Period Payment Interest 1 --PMT($C$4,$C$2,50000) =50000*C4 =B6+1 |--PMT($C$4,$C$2,50000) =F6*$C$4 =B7+1 |--PMT($C$4,$C$2,50000) =F7*$C$4 =B8+1 --PMT($C$4,$C$2,50000) =F8*$C$4 =B9+1 --PMT($C$4,$C$2,50000) =F9*$C$4 | =B10+1 --PMT($C$4,$C$2,50000) =F10*$C$4 =B11+1 |--PMT($C$4,$C$2,50000) =F11*$C$4 =B12+1 --PMT($C$4,$C$2,50000) =F12*$C$4 =B13+1 |--PMT($C$4,$C$2,50000) |=F13*$C$4 | =B14+1 --PMT($C$4,$C$2,50000) =F14*$C$4 =B15+1 |--PMT($C$4,$C$2,50000) =F15*$C$4 =B16+1 --PMT($C$4,$C$2,50000) =F16*$C$4 =B17+1 |--PMT($C$4,$C$2,50000) |=F17*$C$4 =B18+1 --PMT($C$4,$C$2,50000) =F18*$C$4 =B19+1 =-PMT($C$4,$C$2,50000) =F19*$C$4 =B20+1 |--PMT($C$4,$C$2,50000) =F20*$C$4 =B2 1+1 |--PMT($C$4,$C$2,50000) =F21*$C$4 =B22+1 --PMT($C$4,$C$2,50000) =F22*$C$4 =B23+1 --PMT($C$ 4,$C$2,50000) =F23*$C$4 =B24+1 --PMT($C$4,$C$2,50000) =F24*$C$4 Principle =C6-D6 =C7-D7 =C8-D8 =C9-09 =C10-D10 =C11-D11 =C12-D12 =C13-D13 =C14-D14 =C15-D15 =C16-D16 =C17-D17 =C18-D18 =C19-D19 =C20-D20 =C21-D21 =C22-D22 =C23-D23 =C24-D24 =C25-D25 Balance =50000-E6 =F6-E7 =F7-E8 =F8-E9 =F9-E10 =F10-E11 =F11-E12 =F12-E13 =F13-E14 =F14-E15 F15-E16 =F16-17 =F17-E18 =F18-E19 =F19-E20 =F20-E21 =F21-E22 =F22-E23 =F23-E24 =F24-E25

Problem #2

Period	Payment	Interest	Principle	Book Value
			10,000,000	8,388,963
1	1,000,000	1,006,676	10,000,000	8,395,639
2	1,000,000	1,007,477	10,000,000	8,403,115
3	1,000,000	1,008,374	10,000,000	8,411,489
4	1,000,000	1,009,379	10,000,000	8,420,868
5	1,000,000	1,010,504	10,000,000	8,431,372
6	1,000,000	1,011,765	10,000,000	8,443,137
7	1,000,000	1,013,176	10,000,000	8,456,313
8	1,000,000	1,014,758	10,000,000	8,471,071
9	1,000,000	1,016,529	10,000,000	8,487,599
10	1,000,000	1,018,512	10,000,000	8,506,111
11	1,000,000	1,020,733	10,000,000	8,526,845
12	1,000,000	1,023,221	10,000,000	8,550,066
13	1,000,000	1,026,008	10,000,000	8,576,074
14	1,000,000	1,029,129	10,000,000	8,605,203
15	1,000,000	1,032,624	10,000,000	8,637,827
16	1,000,000	1,036,539	10,000,000	8,674,366
17	1,000,000	1,040,924	10,000,000	8,715,290
18	1,000,000	1,045,835	10,000,000	8,761,125
19	1,000,000	1,051,335	10,000,000	8,812,460
20	1,000,000	1,057,495	10,000,000	8,869,955
21	1,000,000	1,064,395	10,000,000	8,934,350
22	1,000,000	1,072,122	10,000,000	9,006,472
23	1,000,000	1,080,777	10,000,000	9,087,249
24	1,000,000	1,090,470	10,000,000	9,177,719
25	1,000,000	1,101,326	10,000,000	9,279,045
26	1,000,000	1,113,485	10,000,000	9,392,530
27	1,000,000	1,127,104	10,000,000	9,519,634
28	1,000,000	1,142,356	10,000,000	9,661,990
29	1,000,000	1,159,439	10,000,000	9,821,429
30	1,000,000	1,178,571	10,000,000	10,000,000

А B с D E F Period Payment Interest 3 4 Principle =10000000 =E4 5 1 6 =E5 7 8 =E6 =E7 9 =E8 =E9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 2 =B6+1 =B7+1 =B8+1 =B9+1 =B10+1 =B11+1 =B12+1 =B13+1 =B14+1 =B15+1 =B16+1 =B17+1 =B18+1 =B19+1 =B20+1 =B21+1 =B22+1 =E4*10% =C5 =C6 =C7 =C8 =C9 =C10 =C11 =C12 =C13 =C14 =C15 =C16 =C17 =C18 =F4*12% =F5*12% =F6*12% =F7*12% =F8*12% =F9*12% =F10*12% =F11*12% =F12*12% =F13*12% =F14*12% =F15*12% =F16*12% =F17*12% =F18*12% =F19*12% =F20*12% =F21*12% =F22*12% =E10 =E11 =E12 =E13 =E14 =E15 =E16 =E17 =E18 =E19 =E20 =E21 =E22 Book Value 8388963.20646652 =F4+(D5-C5) =F5+(D6-C6) =F6+(D7-07) =F7+(D8-C8) =F8+(D9-09) =F9+(D10-C10) =F10+(D11-C11) =F11+(D12-C12) =F12+(D13-C13) =F13+(D14-C14) =F14+(D15-C15) =F15+(D16-C16) =F16+(D17-C17) =F17+(D18-C18) =F18+(D19-C19) =F19+(D20-C20) =F20+(D21-C21) =F21+(D22-C22) =F22+(D23-C23) =C19 =C20 =C21 =C22