MY WEBSITE SPREADSHEET

The spreadsheet Ch3Amortization. xls, Loan tab, produces an amortization table for a loan with regular monthly payments.

Table 3.7 is a snapshot of a part of this spreadsheet. This table distorts the spreadsheet slightly in that because the spreadsheet is too wide to fit on a printed page, I’ve taken the input data—the data to the left of the green line in the spread – sheet—and put it on top in the table.

The input variables for these calculations are

Start Month the month when you take the loan; a number between 1 and 12;

Start Year the year when you take the loan;

Nr Mnthly Pmts if the loan is described in years, this is 12 (number of years);

Principal the amount you’re borrowing;

Rate the annual percentage rate (APR).

The actual date of the loan is not shown. The output columns are

Подпись:a running count of the payments. Zero is the start date; the month and year of each payment; this is the balance after the current payment; the monthly payment;

the interest accrued between last month and this month; a running total of the interest payments in each calendar year.

Table 3.7 Snapshot of the Loan Tab of Spreadsheet Ch3Amortization. xls

Start Month:

7

Start Year:

2005

Nr Mnthly Pmts

180

Principal

$350,000

Rate

6.94%

Pmt Nr

Mnth

Year

Balance ($)

Payment ($)

Interest ($)

Tot Int/Year ($)

0

7

2005

350,000.00

0.00

0.00

0.00

1

8

2005

348,890.00

3,134.17

2,024.17

2,024.17

2

9

2005

347,773.57

3,134.17

2,017.75

4,041.91

3

10

2005

346,650.69

3,134.17

2,011.29

6,053.20

4

11

2005

345,521.32

3,134.17

2,004.80

8,058.00

5

12

2005

344,385.42

3,134.17

1,998.26

10,056.27

6

1

2006

343,242.94

3,134.17

1,991.70

1,991.70

7

2

2006

342,093.86

3,134.17

1,985.09

3,976.78

8

3

2006

340,938.13

3,134.17

1,978.44

5,955.23

9

4

2006

339,775.72

3,134.17

1,971.76

7,926.99

10

5

2006

338,606.59

3,134.17

1,965.04

9,892.02

11

6

2006

337,430.69

3,134.17

1,958.27

11,850.30

12

7

2006

336,248.00

3,134.17

1,951.47

13,801.77

13

8

2006

335,058.46

3,134.17

1,944.63

15,746.41

14

9

2006

333,862.04

3,134.17

1,937.75

17,684.16

15

10

2006

332,658.71

3,134.17

1,930.84

19,615.00

16

11

2006

331,448.42

3,134.17

1,923.88

21,538.87

17

12

2006

330,231.12

3,134.17

1,916.88

23,455.75

18

1

2007

329,006.79

3,134.17

1,909.84

1,909.84

19

2

2007

327,775.38

3,134.17

1,902.76

3,812.59

20

3

2007

326,536.84

3,134.17

1,895.63

5,708.23

178

5

2020

6,214.38

3,134.17

53.75

444.89

179

6

2020

3,116.15

3,134.17

35.94

480.83

180

7

2020

0.00

3,134.17

18.02

498.85

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>