Creating a payment schedule. Any keeners out there?

[patrickboileau]

Type// //1st payment // //Value// //Occurance// Payment//
Opening// //01/02/2002// //27,420.00// //1// 27,420.00//
Monthly// //02/03/2002// //32,904.00// //36// //914//
Yearly// //02/02/2003// //32,904.00// //2// 16,452.00//
Monthly// //02/12/2003// //82,260.00// //100// 1,557.49//
Yearly// //02/01/2005// //98,712.00// //6// 29,834.17//

I am looking for the most efficient way to expand this information into a payment table. The only way I know how is with "if" and date functions. CAN SOMEONE HELP ME? What would be the best way to set things up so that when the variables change everything works out nicely in the schedule.

THANKS A BUNCH!

PAt

 

[vane0326]

Here Sample that this might help.
I dont know why this comes out &nbsp everytime when I post something. My worksheet does'nt have that.

House Loan TABLE.xls
	A	B	C	D	E	F	G
1	LoanData
2	InterestRate(Annual)	5.00%	TimeBasis		4	TimeBasisValues
3	Amortization(Years)	30	TimeFactor	12		Annual
4	Principal	$300,000	AdjustedRate	0.4%		Semi-Annual
5	BalloonPayment	$0	TotalPeriods	360		Quarterly
6	PaymentType					Monthly
7						Bi-Weekly
8	AmortizationSchedule
9	Period	Payment	Principal	Interest	CumulativePrincipal	CumulativeInterest	RemainingPrincipal
10	1	($1,610.46)	($360.46)	($1,250.00)	($360.46)	($1,248.50)	$299,639.54
11	2	($1,610.46)	($361.97)	($1,248.50)	($722.43)	($2,498.50)	$299,277.57
12	3	($1,610.46)	($363.48)	($1,246.99)	($1,085.91)	($3,745.49)	$298,914.09
13	4	($1,610.46)	($364.99)	($1,245.48)	($1,450.90)	($4,990.96)	$298,549.10
14	5	($1,610.46)	($366.51)	($1,243.95)	($1,817.41)	($6,234.92)	$298,182.59
15	6	($1,610.46)	($368.04)	($1,242.43)	($2,185.44)	($7,477.35)	$297,814.56
16	7	($1,610.46)	($369.57)	($1,240.89)	($2,555.01)	($8,718.24)	$297,444.99
Sheet1

 

[yee388]

...I dont know why this comes out &nbsp everytime when I post something. My worksheet does'nt have that....

See Pookie's note: http://www.mrexcel.com/board2/viewtopic.php?t=131105

 

[vane0326]

Thanks yee388 Hopefuly that junk does not come out. Thanks for the input.
Here is the sample again.

House Loan TABLE.xls
	A	B	C	D	E	F	G
1	Loan Data
2	Interest Rate (Annual)	5.00%	Time Basis		4	Time Basis Values
3	Amortization (Years)	30	Time Factor	12		Annual
4	Principal	$300,000	Adjusted Rate	0.4%		Semi-Annual
5	Balloon Payment	$0	Total Periods	360		Quarterly
6	Payment Type					Monthly
7						Bi-Weekly
8	Amortization Schedule
9	Period	Payment	Principal	Interest	Cumulative Principal	Cumulative Interest	Remaining Principal
10	1	($1,610.46)	($360.46)	($1,250.00)	($360.46)	($1,248.50)	$299,639.54
11	2	($1,610.46)	($361.97)	($1,248.50)	($722.43)	($2,498.50)	$299,277.57
12	3	($1,610.46)	($363.48)	($1,246.99)	($1,085.91)	($3,745.49)	$298,914.09
13	4	($1,610.46)	($364.99)	($1,245.48)	($1,450.90)	($4,990.96)	$298,549.10
14	5	($1,610.46)	($366.51)	($1,243.95)	($1,817.41)	($6,234.92)	$298,182.59
15	6	($1,610.46)	($368.04)	($1,242.43)	($2,185.44)	($7,477.35)	$297,814.56
16	7	($1,610.46)	($369.57)	($1,240.89)	($2,555.01)	($8,718.24)	$297,444.99
17	8	($1,610.46)	($371.11)	($1,239.35)	($2,926.13)	($9,957.59)	$297,073.87
18	9	($1,610.46)	($372.66)	($1,237.81)	($3,298.78)	($11,195.40)	$296,701.22
19	10	($1,610.46)	($374.21)	($1,236.26)	($3,672.99)	($12,431.66)	$296,327.01
Sheet1