Visionary Products purchased a building for 1000000 with 20

Visionary Products purchased a building for $1,000,000 with 20% cash down payment and the remainder paid in installments with 3% interest. Using excel, prepare an amortization schedule for the mortgage. I guess its okay to assume 30 year.

Solution

cost of building

Year

1000000

amount of loan

1000000*(1-.2)

800000

Period

It is assumed annual payment

30 Years

interest rate

3%

Yearly payment = Using PMT function in MS excel

PMT(rate,nper,pv,fv,type) =pmt(3%,30,800000,0,0)

($40,815.41)

Year

Balance due at the beginning

Yearly payment

Amount of interest

amount of principal

balance at year end

0

800000

1

800000

40815.41

24000

16815.41

783184.6

2

783184.6

40815.41

23495.54

17319.87

765864.7

3

765864.7

40815.41

22975.94

17839.47

748025.2

4

748025.2

40815.41

22440.76

18374.65

729650.6

5

729650.6

40815.41

21889.52

18925.89

710724.7

6

710724.7

40815.41

21321.74

19493.67

691231

7

691231

40815.41

20736.93

20078.48

671152.6

8

671152.6

40815.41

20134.58

20680.83

650471.7

9

650471.7

40815.41

19514.15

21301.26

629170.5

10

629170.5

40815.41

18875.11

21940.3

607230.2

11

607230.2

40815.41

18216.91

22598.5

584631.7

12

584631.7

40815.41

17538.95

23276.46

561355.2

13

561355.2

40815.41

16840.66

23974.75

537380.5

14

537380.5

40815.41

16121.41

24694

512686.5

15

512686.5

40815.41

15380.59

25434.82

487251.6

16

487251.6

40815.41

14617.55

26197.86

461053.8

17

461053.8

40815.41

13831.61

26983.8

434070

18

434070

40815.41

13022.1

27793.31

406276.7

19

406276.7

40815.41

12188.3

28627.11

377649.6

20

377649.6

40815.41

11329.49

29485.92

348163.6

21

348163.6

40815.41

10444.91

30370.5

317793.1

22

317793.1

40815.41

9533.794

31281.62

286511.5

23

286511.5

40815.41

8595.346

32220.06

254291.5

24

254291.5

40815.41

7628.744

33186.67

221104.8

25

221104.8

40815.41

6633.144

34182.27

186922.5

26

186922.5

40815.41

5607.676

35207.73

151714.8

27

151714.8

40815.41

4551.444

36263.97

115450.8

28

115450.8

40815.41

3463.525

37351.89

78098.94

29

78098.94

40815.41

2342.968

38472.44

39626.49

30

39626.49

40815.41

1188.795

39626.62

0

cost of building	Year	1000000
amount of loan	1000000*(1-.2)	800000
Period	It is assumed annual payment	30 Years
interest rate		3%
Yearly payment = Using PMT function in MS excel	PMT(rate,nper,pv,fv,type) =pmt(3%,30,800000,0,0)	($40,815.41)
Year	Balance due at the beginning	Yearly payment	Amount of interest	amount of principal	balance at year end
0					800000
1	800000	40815.41	24000	16815.41	783184.6
2	783184.6	40815.41	23495.54	17319.87	765864.7
3	765864.7	40815.41	22975.94	17839.47	748025.2
4	748025.2	40815.41	22440.76	18374.65	729650.6
5	729650.6	40815.41	21889.52	18925.89	710724.7
6	710724.7	40815.41	21321.74	19493.67	691231
7	691231	40815.41	20736.93	20078.48	671152.6
8	671152.6	40815.41	20134.58	20680.83	650471.7
9	650471.7	40815.41	19514.15	21301.26	629170.5
10	629170.5	40815.41	18875.11	21940.3	607230.2
11	607230.2	40815.41	18216.91	22598.5	584631.7
12	584631.7	40815.41	17538.95	23276.46	561355.2
13	561355.2	40815.41	16840.66	23974.75	537380.5
14	537380.5	40815.41	16121.41	24694	512686.5
15	512686.5	40815.41	15380.59	25434.82	487251.6
16	487251.6	40815.41	14617.55	26197.86	461053.8
17	461053.8	40815.41	13831.61	26983.8	434070
18	434070	40815.41	13022.1	27793.31	406276.7
19	406276.7	40815.41	12188.3	28627.11	377649.6
20	377649.6	40815.41	11329.49	29485.92	348163.6
21	348163.6	40815.41	10444.91	30370.5	317793.1
22	317793.1	40815.41	9533.794	31281.62	286511.5
23	286511.5	40815.41	8595.346	32220.06	254291.5
24	254291.5	40815.41	7628.744	33186.67	221104.8
25	221104.8	40815.41	6633.144	34182.27	186922.5
26	186922.5	40815.41	5607.676	35207.73	151714.8
27	151714.8	40815.41	4551.444	36263.97	115450.8
28	115450.8	40815.41	3463.525	37351.89	78098.94
29	78098.94	40815.41	2342.968	38472.44	39626.49
30	39626.49	40815.41	1188.795	39626.62	0