There are two formula in excel spreadsheet you can use.

Payment: PMT(rate, nper, pv, fv, type) e.g. PMT(0.04/12, 360, 200000,0,0)

4% interest rate, 30 years (360 payment) and 200000 loan amount.

 

Interest Payment: IPMT(rate, per, Nper, Pv, Fv), e.g. IPMT(0.04/12, 12, 360, 200000, 0)

12 is the 12th month or the end of first year.

The principle paid that month is PMT - IPMT and the ending balance is:

Total loan amount - (PMT - IPMT), e.g. 200000 - (PMT - IPMT).

You can build a spreadsheet like this:

		Year	Payment Index	Interest	Principle	Payment	Principle
Interest Rate	4.000%	0.08	1	($666.67)	($288.16)	($954.83)	199,711.84
Term	360	0.17	2	($665.71)	($289.12)	($954.83)	199,422.71
Loan Amount	$      200,000	0.25	3	($664.74)	($290.09)	($954.83)	199,132.62
		0.33	4	($663.78)	($291.06)	($954.83)	198,841.57
5 Year Int.	($38,185)	0.42	5	($662.81)	($292.03)	($954.83)	198,549.54
10 Year Int.	($72,148)	0.50	6	($661.83)	($293.00)	($954.83)	198,256.54
15 Year Int.	($100,955)	0.58	7	($660.86)	($293.98)	($954.83)	197,962.57
20 Year Int.	($123,468)	0.67	8	($659.88)	($294.96)	($954.83)	197,667.61
30 Year Int.	($143,739)	0.75	9	($658.89)	($295.94)	($954.83)	197,371.67
		0.83	10	($657.91)	($296.93)	($954.83)	197,074.75
		0.92	11	($656.92)	($297.91)	($954.83)	196,776.83
		1.00	12	($655.92)	($298.91)	($954.83)	196,477.93

所有跟帖： 

• 试了一下。好用。谢谢 -觉觉- ♀ (0 bytes) () 02/07/2012 postreply 15:09:43