variable installment months

[mohamed ali abdelgawad]

Hi Dears
I am not an accountant person that's why i can't calculate this.
so please help me

i have sheet to calculate the installment amounts of price of the apartment.

it's 2500000 $
if cst want to pay 10% (variable percent) in the start which 250000 $ .
then install the rest of amount on 1 , 3 , 4 till 10 years ( as per my chaise) every 1 , 3 , 4 till 12 month ( as per my chaise).

Here is the difficulty I face
at the end of first year i must collect (variable percent) for example 18% (installment amount of this year + start amount)
and at the end of second year i must collect (variable percent) for example 30% (installment amount 2 years + start amount)
and at the end of third year i must collect (variable percent) for example 48% (installment amount 3 years + start amount)

how can i do this
the difficulty I face
cst want to choose in 1st , 2nd , 3rd till 9th year every 3 (variable) months and the rest of years every 6 (variable) months

how i do all of this.
you can download example sheet from here


i know i asked too much but i'am new in this field and i want to understand.
i searched a lot but i only find the PMT or FV it may help in this case but i don'e find what help me in this case .


thanks

 

[KRice]

That's good news...so added amounts and intermediate cumulative amounts are the inputs. The worksheet I posted earlier does that, but you also mentioned a wish to have greater flexibility in changing the loan terms. In the example below, you can make inputs to the yellow cells, and the entire amortization table should populate. Be sure to follow the instruction shown in the red cell and enter 100 in the cell corresponding to the last payment. I made the rate computation more general so that intermediate cumulative payment milestones and additional payment amounts can occur at any time, and as the loan term and payment frequency values can change, extra attention will be needed to ensure that the manual entries are made at the correct payment number. I ran into a problem trying to post the entire table (character limit), so the main part of the table is shown below. The formulas used in the main table are copied down for a total of 120 rows (because the maximum term is 10 years and the most frequent payment plan is 12 payments/year). You shouldn't have any difficulty setting up the full table, but you might have to give some extra attention to the array formula in the rate computation column, confirmed with Control-Shift-Enter.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2					Contract price & deposit details			Term of loan & parts		Payment frequency			Number of payments
3					2,500,000	Contract Price		4	Term of loan (y)				18	Total # pmt pds
4					12.00%	Deposit %		3	Term 1st part (y)	3	1st part: pmt/X mo		12	1st part: # pmts
5					300,000	Deposit amt.		1	Term 2nd part (y)	2	2nd part: pmt/Y mo		6	2nd part: # pmts
6					2,200,000	Balance to pay
7
8
9		Pmt #	Year	Period in Yr	Year-Pmt	% of P paid each pmt period	Installment Amt paid by end of each pmt period	Required intermediate cumulative payment milestones by end of period	Required intermediate cumulative pmt percentages by end of period (% of contract price)	Total amt actually paid by end of period	Added amt to pay	Added amt as % of P
10					Downpayment			300000	enter 100% below for payment 18
11		1	1	1	Year 1 Pmt 1	5.25%	131250			431250
12		2	1	2	Year 1 Pmt 2	5.25%	131250			562500
13		3	1	3	Year 1 Pmt 3	5.25%	131250			693750
14		4	1	4	Year 1 Pmt 4	5.25%	131250	825000	33.00%	825000	0	0.00%
15		5	2	1	Year 2 Pmt 1	3.50%	87500			912500
16		6	2	2	Year 2 Pmt 2	3.50%	87500	1000000	40.00%	1000000
17		7	2	3	Year 2 Pmt 3	6.00%	150000			1175000	25000	1.00%
18		8	2	4	Year 2 Pmt 4	6.00%	150000			1325000
19		9	3	1	Year 3 Pmt 1	6.00%	150000			1475000
20		10	3	2	Year 3 Pmt 2	5.50%	137500			1662500	50000	2.00%
21		11	3	3	Year 3 Pmt 3	5.50%	137500			1800000
22		12	3	4	Year 3 Pmt 4	5.50%	137500			1937500
23		13	4	1	Year 4 Pmt 1	5.50%	137500	2075000	83.00%	2075000
24		14	4	2	Year 4 Pmt 2	3.40%	85000			2160000
25		15	4	3	Year 4 Pmt 3	3.40%	85000			2245000
26		16	4	4	Year 4 Pmt 4	3.40%	85000			2330000
27		17	4	5	Year 4 Pmt 5	3.40%	85000			2415000
28		18	4	6	Year 4 Pmt 6	3.40%	85000	2500000	100.00%	2500000
29
30
31
32
33
34
Mohamed_rev2

Cell Formulas
Range	Formula
H4	H4	=IFS(($H$3=1),1,($H$3=2),2,AND($H$3>=3,$H$3<=10),3,OR($H$3<1,$H$3>10),"not valid term")
H5	H5	=H3-H4
M3	M3	=$H$4*12/$J$4+($H$3-$H$4)*12/$J$5
M4	M4	=$H$4*12/$J$4
M5	M5	=($H$3-$H$4)*12/$J$5
E5	E5	=E3*E4
E6	E6	=E3-E5
H10	H10	=$E$5
I10	I10	="enter 100% below for payment "&M3
B11:B34	B11	=IFERROR(IF(OFFSET(B11,-1,0)+1<=$M$3,OFFSET(B11,-1,0)+1,""),"")
C11:C34	C11	=IF($B11="","",IF($B11<=$H$4*12/$J$4,INT(($B11-1)*$J$4/12)+1,INT(($B11-$H$4*12/$J$4-1)*$J$5/12)+$H$4+1))
D11:D34	D11	=IF($B11="","",IF($B11<=$H$4*12/$J$4,MOD(($B11-1),12/$J$4)+1, MOD(($B11-$H$4*12/$J$4-1),12/$J$5)+1))
E11:E34	E11	=IF($B11="","",CONCATENATE("Year ", C11," Pmt ",D11))
F11:F34	F11	=IF($B11="","",(OFFSET(H11,MATCH(FALSE,ISBLANK(I11:I$130),0)-1,0)-$H$10-SUM(G$10:G10)-SUM(K$11:K11))/MATCH(FALSE,ISBLANK(I11:I$130),0)/$E$3)
G11:G34	G11	=IF($B11="","",$E$3*F11)
K11:K34,H11:H34	H11	=IF($B11="","",IF(ISNUMBER(I11),$E$3*I11,""))
J11:J34	J11	=IF($B11="","",SUM($H$10,SUM(G$11:G11),SUM(K$11:K11)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Data Validation
Cell	Allow	Criteria
J4:J5	List	1,2,3,4,6,12
H3	List	2,3,4,5,6,7,8,9,10