Calculate Monthly Payments vs Lump Sum

[sduconn98]

I need to calculate the impact of allowing our customers to buy a product at full price today or to pay some % up front and then a monthly payment.

For example, they would be able to choose to pay $250 one time or $50 up front and then $200 over the next 11 months (split evenly) with a cost of capital of 1%/month.

What's the best way to set this up? Any help would be appreciated.

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G	H
1		Method 1		Method 2
2				Payment	Interest	Prin Reduc	UnPaid
3	Paid Today	$ 250.00						$ 50.00
4	Balance Due	-					$ 200.00
5	1 month later			$17.77	$2.00	15.77	184.23
6	2 month later			$17.77	$1.84	15.93	168.30
7	3 month later			$17.77	$1.68	16.09	152.22
8	4 month later			$17.77	$1.52	16.25	135.97
9	5 month later			$17.77	$1.36	16.41	119.56
10	6 month later			$17.77	$1.20	16.57	102.98
11	7 month later			$17.77	$1.03	16.74	86.24
12	8 month later			$17.77	$0.86	16.91	69.33
13	9 month later			$17.77	$0.69	17.08	52.26
14	10 month later			$17.77	$0.52	17.25	35.01
15	11 month later			$17.77	$0.35	17.42	17.59
16	12 month later			$17.77	$0.18	17.59	(0.00)
17		Total Paid		$213.24	$13.24	$200.00
18
19
20	Amt Financed:	$ 200.00
21	Annual Int Rate	12%
22	Term (Months)	12
23
24	Monthly Payment
25	Computed to
26	Pay off	$17.77
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B4	=250-B3
B20	=G4
B26	=ROUND(PMT(B21/12,B22,-B20),2)
G4	=250-H3
G5	=G4-F5
G6	=G5-F6
G7	=G6-F7
G8	=G7-F8
G9	=G8-F9
G10	=G9-F10
G11	=G10-F11
G12	=G11-F12
G13	=G12-F13
G14	=G13-F14
G15	=G14-F15
G16	=G15-F16
D5	=$B$26
D6	=$B$26
D7	=$B$26
D8	=$B$26
D9	=$B$26
D10	=$B$26
D11	=$B$26
D12	=$B$26
D13	=$B$26
D14	=$B$26
D15	=$B$26
D16	=$B$26
D17	=SUM(D5:D16)
E5	=G4*$B$21*(30/360)
E6	=G5*$B$21*(30/360)
E7	=G6*$B$21*(30/360)
E8	=G7*$B$21*(30/360)
E9	=G8*$B$21*(30/360)
E10	=G9*$B$21*(30/360)
E11	=G10*$B$21*(30/360)
E12	=G11*$B$21*(30/360)
E13	=G12*$B$21*(30/360)
E14	=G13*$B$21*(30/360)
E15	=G14*$B$21*(30/360)
E16	=G15*$B$21*(30/360)
E17	=SUM(E5:E16)
F5	=D5-E5
F6	=D6-E6
F7	=D7-E7
F8	=D8-E8
F9	=D9-E9
F10	=D10-E10
F11	=D11-E11
F12	=D12-E12
F13	=D13-E13
F14	=D14-E14
F15	=D15-E15
F16	=D16-E16
F17	=SUM(F5:F16)
A5	=ROW()-4 & " month later"
A6	=ROW()-4 & " month later"
A7	=ROW()-4 & " month later"
A8	=ROW()-4 & " month later"
A9	=ROW()-4 & " month later"
A10	=ROW()-4 & " month later"
A11	=ROW()-4 & " month later"
A12	=ROW()-4 & " month later"
A13	=ROW()-4 & " month later"
A14	=ROW()-4 & " month later"
A15	=ROW()-4 & " month later"
A16	=ROW()-4 & " month later"