Looking for a specific loan calculator....

[Travis Elliott]

Hi there...

I am purchasing a home on contract and the terms are as follows. 3.5 percent interest rate, balloon in four years with a principal amount of 27,000. So my PI payment is only 130.00 per month. The seller wants me to pay $ 350.00 per month so I am in search of a good calculator to keep track of all the payments. Plus in a few months I am going to pay a thousand per month.

Any suggestions?

Thanks

[Jon Holdman]

Travis Elliott, what the seller is saying is he want's to specify the interest rate and the monthly payment. What you need to calculate is the term. Normally, a lender would specify the rate and term and would calculate the payment. But as long as you have three of the four parameters for the calculation, you can calculate the other. In Excel the function you want is NPER. The specific formula is =NPER(3.5%/12,-350,27000). The result is 87.519. That's the length of you loan, in months.

Now, you say you're going to pay $1000 per month starting in a few months. Set up a spreadsheet in Excel. Each month's interest is the current balance * 3.5%/12. Part of the payment goes to pay the interest. Whatever is left reduces the principle. The first month's interest is $78.75. If you payment is $350, then the remaining $271.25 goes to principle. So, after one payment, the principle balance is $26,728.75. So, the second month's interest is $26,728.75 * (3.5%/12) = $77.96.

At some point you start paying more. The interest part is still calculated the same. There's just a lot more left over for principle, so it goes down faster. If you make, say, six $350 payment, then start paying $1000 a month then after 32 total payments (i.e., 6@ $350 and then 26 @$1000) the loan balance will be $385.13 and you can pay that off with a payment of $386.25.

Here's my complete spreadsheet, but its not going to show up very well:

rate 3.50%
payment balance payment interest principle
1 $27,000.00 $350.00 $78.75 $271.25
2 $26,728.75 $350.00 $77.96 $272.04
3 $26,456.71 $350.00 $77.17 $272.83
4 $26,183.87 $350.00 $76.37 $273.63
5 $25,910.24 $350.00 $75.57 $274.43
6 $25,635.82 $350.00 $74.77 $275.23
7 $25,360.59 $1,000.00 $73.97 $926.03
8 $24,434.55 $1,000.00 $71.27 $928.73
9 $23,505.82 $1,000.00 $68.56 $931.44
10 $22,574.38 $1,000.00 $65.84 $934.16
11 $21,640.22 $1,000.00 $63.12 $936.88
12 $20,703.34 $1,000.00 $60.38 $939.62
13 $19,763.73 $1,000.00 $57.64 $942.36
14 $18,821.37 $1,000.00 $54.90 $945.10
15 $17,876.26 $1,000.00 $52.14 $947.86
16 $16,928.40 $1,000.00 $49.37 $950.63
17 $15,977.78 $1,000.00 $46.60 $953.40
18 $15,024.38 $1,000.00 $43.82 $956.18
19 $14,068.20 $1,000.00 $41.03 $958.97
20 $13,109.23 $1,000.00 $38.24 $961.76
21 $12,147.47 $1,000.00 $35.43 $964.57
22 $11,182.90 $1,000.00 $32.62 $967.38
23 $10,215.52 $1,000.00 $29.80 $970.20
24 $9,245.31 $1,000.00 $26.97 $973.03
25 $8,272.28 $1,000.00 $24.13 $975.87
26 $7,296.40 $1,000.00 $21.28 $978.72
27 $6,317.69 $1,000.00 $18.43 $981.57
28 $5,336.11 $1,000.00 $15.56 $984.44
29 $4,351.68 $1,000.00 $12.69 $987.31
30 $3,364.37 $1,000.00 $9.81 $990.19
31 $2,374.18 $1,000.00 $6.92 $993.08
32 $1,381.11 $1,000.00 $4.03 $995.97
33 $385.13 $386.25 $1.12 $385.13