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

 

[CountTepes]

That simplifies it. Just put this in every cell in C2 to C41

=($H$2-SUM($F$2:$F$41))/40

 

[mohamed ali abdelgawad]

That simplifies it. Just put this in every cell in C2 to C41

=($H$2-SUM($F$2:$F$41))/40

thanks for your support .
but may i can't deliver my goal to you.
please open sheet from here
i want formula to get the same result of the left example .. that's all what i want

 

[CountTepes]

I'm sorry. I am at work and can neither upload or download files and at home I do not have either a windows computer, nor MS office. I'm all Linux at home.

 

[mohamed ali abdelgawad]

I'm sorry. I am at work and can neither upload or download files and at home I do not have either a windows computer, nor MS office. I'm all Linux at home.

ok
thanks for you support and your patience

 

[mohamed ali abdelgawad]

thanks for your support .
but may i can't deliver my goal to you.
please open sheet from here
i want formula to get the same result of the left example .. that's all what i want

View attachment 8285

anyone reply to me please

 

[mohamed ali abdelgawad]

thanks for your support .
but may i can't deliver my goal to you.
please open sheet from here
i want formula to get the same result of the left example .. that's all what i want

View attachment 8285

anyone help me please

 

[KRice]

Mohamed,
I am unclear on some details, but this may do what you want. Try it and let me know. You should be able to change the contract price, the downpayment percentage, and the values in the six boxes. I suppose those boxes are used to conform with contractual requirements, where some percentage of the loan amount is to be paid by the end of years 1, 2, and 3, and there is some agreement about supplemental payments made at the end of those same years. I am not entirely satisfied with the formulas in the table column B, as I have wired into the formulas the number of rows (or quarterly periods) represented by intervals between entries in column E. So you will find a divisor of 4 in the formulas through year 3, and a divisor of 28 for years 4 through 10. I've added some helper columns to the table to provide insight into how the installment amounts are adjusted depending on the 8 input parameters.

MrExcel_2.xlsm
	A	B	C	D	E	F	G	H
1		1000000	Contract Price (P)
2		10	Total term in years (N)
3
4		Dep. % (dp)	Dep Amt.	Remaining Balance
5		10.00%	100000	900000
6
7	Column totals -->		750000				150000
8	Year-Qtr	% of P paid per Qtr	Installment Amt paid per Qtr	Cumulative Amt that should be collected by end of period	Cumulative Percent of Contract Price to be collected by end of period (%)	Total amt actually paid by end of period	Added amt to collect/pay	Added amt expressed as percentage of P
9	at loan signing			100000
10	Year1 Q1	2.00%	20000			120000
11	Year1 Q2	2.00%	20000			140000
12	Year1 Q3	2.00%	20000			160000
13	Year1 Q4	2.00%	20000	180000	18.0%	180000	0	0.0%
14	Year2 Q1	1.75%	17500			197500
15	Year2 Q2	1.75%	17500			215000
16	Year2 Q3	1.75%	17500			232500
17	Year2 Q4	1.75%	17500	300000	30.0%	300000	50000	5.0%
18	Year3 Q1	2.00%	20000			320000
19	Year3 Q2	2.00%	20000			340000
20	Year3 Q3	2.00%	20000			360000
21	Year3 Q4	2.00%	20000	480000	48.0%	480000	100000	10.0%
22	Year4 Q1	1.86%	18571			498571
23	Year4 Q2	1.86%	18571			517143
24	Year4 Q3	1.86%	18571			535714
25	Year4 Q4	1.86%	18571			554286
26	Year5 Q1	1.86%	18571			572857
27	Year5 Q2	1.86%	18571			591429
28	Year5 Q3	1.86%	18571			610000
29	Year5 Q4	1.86%	18571			628571
30	Year6 Q1	1.86%	18571			647143
31	Year6 Q2	1.86%	18571			665714
32	Year6 Q3	1.86%	18571			684286
33	Year6 Q4	1.86%	18571			702857
34	Year7 Q1	1.86%	18571			721429
35	Year7 Q2	1.86%	18571			740000
36	Year7 Q3	1.86%	18571			758571
37	Year7 Q4	1.86%	18571			777143
38	Year8 Q1	1.86%	18571			795714
39	Year8 Q2	1.86%	18571			814286
40	Year8 Q3	1.86%	18571			832857
41	Year8 Q4	1.86%	18571			851429
42	Year9 Q1	1.86%	18571			870000
43	Year9 Q2	1.86%	18571			888571
44	Year9 Q3	1.86%	18571			907143
45	Year9 Q4	1.86%	18571			925714
46	Year10 Q1	1.86%	18571			944286
47	Year10 Q2	1.86%	18571			962857
48	Year10 Q3	1.86%	18571			981429
49	Year10 Q4	1.86%	18571	1000000	100.0%	1000000	0	0.0%
Sheet26

Cell Formulas
Range	Formula
C5	C5	=B1*B5
D5	D5	=B1-C5
C7,G7	C7	=SUM(C10:C49)
D9	D9	=C5
B10:B21	B10	=IF(ISNUMBER(E10),($D10- IFERROR(LOOKUP(2,1/(ISNUMBER(D$9:D9)),D$9:D9),0) -G10)/$B$1/4,B11)
C10:C49	C10	=$B$1*B10
G10:G49,D10:D49	D10	=IF(ISNUMBER(E10),$B$1*E10,"")
B22:B49	B22	=IF(ISNUMBER(E22),($D22- LOOKUP(2,1/(ISNUMBER(D$10:D21)),D$10:D21) -G22)/$B$1/28,B23)
F10:F49	F10	=SUM($D$9,SUM(C$10:C10),SUM(G$10:G10))

 

[mohamed ali abdelgawad]

Mohamed,
I am unclear on some details, but this may do what you want. Try it and let me know. You should be able to change the contract price, the downpayment percentage, and the values in the six boxes. I suppose those boxes are used to conform with contractual requirements, where some percentage of the loan amount is to be paid by the end of years 1, 2, and 3, and there is some agreement about supplemental payments made at the end of those same years. I am not entirely satisfied with the formulas in the table column B, as I have wired into the formulas the number of rows (or quarterly periods) represented by intervals between entries in column E. So you will find a divisor of 4 in the formulas through year 3, and a divisor of 28 for years 4 through 10. I've added some helper columns to the table to provide insight into how the installment amounts are adjusted depending on the 8 input parameters.

Thanks dear KRice.
it's worked and get the required result
but i don't know how i apply this in my sheet because in my main sheet i have many changes like

1. cst may choose to pay every 1 , 2 , 3 , 4 , 6 or 12 month
2. also he may choose pay every 1 , 2 , 3 , 4 , 6 or 12 starting from fourth or seventh year for example
3. also he can choose from 2 years to 10 years.

i applied all of this on my sheet but what i cant do is this distribution.
you will end all of this by help me get formula in k8 , k13 and k18 in this sheet

waiting for your support.

 

[KRice]

Great, so this is a start toward a solution. Could you explain the "Added amounts" in more detail, please? In my example, I moved their calculation to columns (G & H) to make it clear that they are not computed the same way as the installment payments. Are these "Added amounts" required by the contract (i.e., user specified)? Do they always occur at the end of years 1, 2, and 3? I don't understand why they are necessary, unless it is a way to reduce a customer's installment payments throughout the year with the promise to make a lump sum payment at the end of the year to make up for any shortfall. Is this correct?

I am still not clear about three things:

1. added amounts at end of years 1, 2, and 3;
2. collected amounts at end of years 1, 2, and 3; and
3. periodic rates for installments (payments)

You need to specify any two of those, and the third can be determined. In the worksheet I posted, there are inputs for #1 and #2, and then the periodic rates (#3) are determined. If you want something different, please let me know which two you want to control as inputs.

If the "added amounts" are user-specified and the "collected amounts at years 1, 2, and 3" are also user-specified, then as an example, we consider what happens during year 1 for a 1,000,000 loan and a 10% down payment, and quarterly payments at 0% interest. If you want the "minimum amount collected" to be 17.5% of the contract price, then we know that 175,000 must be paid (1000000 * 0.175) by the end of year 1. This total year 1 amount has three components: the initial down payment, installment payments, and the added amount paid at the end of year 1. If you want the added amount to be 0, then the total of the installments paid in year 1 would be 175000 - 100000 (the down payment) - 0 (the added amount) = 75000 (from installments). And dividing this total year 1 installment amount by 4 (quarterly payments) gives 18750 per quarter, and then converting that quarterly amount to an effective quarterly rate relative to the contract price...18750/1000000 = 1.875% quarterly rate. If the added amount is anything else, then the same/similar formula is used to find the new periodic payment rate/amount.

For some reason, your example2 worksheet uses a quarterly rate of 2.25% (equivalent to quarterly installments of 22500) for the same 175000 "minimum amount collected". This means that the total year 1 installments would be 90,000, and when added to the down payment of 100,000, the customer would have paid a total of 190,000 by the end of year 1. They overpaid, and the "added amount" in year 1 would be -15,000...the customer would be given a refund of 15,000. Is that the intent?

If you could clarify the points above and explain more about the "added amounts", that would be helpful.

I don't see any issues making this worksheet adaptable to accommodate the options you've mentioned, with sheet inputs of:
Contract price
Deposit/down payment percentage
Customer chooses total term of loan, integers from 2-10 years
Customer chooses to pay every 1,2,3,4,6, or 12 months for the 1st part of the loan term
Customer chooses to pay every 1,2,3,4,6, or 12 months for the 2nd part of the loan term
User-specified end-of-year cumulative percentages that must be paid during the 1st part of the loan

In your example2 sheet, you have end-of-year cumulative percentages for years 1,2, and 3. What happens if a customer chooses a loan term of 2 years? Does this collected amount profile reduce to one input for end-of-year 1 and 100% paid by the end of year 2?

Mine may not be the correct interpretation. If you could upload a portion of your worksheet here using XL2BB that would facilitate assistance. I couldn't find any formulas in your linked file, so it would be better to use the XL2BB tool provided here for sharing.

 

[mohamed ali abdelgawad]

Thanks for your interest in my case
you was right i must enter added amount manually like you did in your sheet
i was thought that it calculated cell but after refer to finance person who design this installment module , he told me that This number must be specified first and then we calculate the months percent based on it.
thanks again and again for your great support