variable installment months

mohamed ali abdelgawad

Board Regular
Joined
Nov 18, 2016
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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
 
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
ABCDEFGHIJKLMN
1
2Contract price & deposit detailsTerm of loan & partsPayment frequencyNumber of payments
32,500,000Contract Price4Term of loan (y)18Total # pmt pds
412.00%Deposit %3Term 1st part (y)31st part: pmt/X mo121st part: # pmts
5300,000Deposit amt.1Term 2nd part (y)22nd part: pmt/Y mo62nd part: # pmts
62,200,000Balance to pay
7
8
9Pmt #YearPeriod in YrYear-Pmt% of P paid each pmt period Installment Amt paid by end of each pmt periodRequired intermediate cumulative payment milestones by end of periodRequired intermediate cumulative pmt percentages by end of period (% of contract price)Total amt actually paid by end of periodAdded amt to payAdded amt as % of P
10Downpayment300000enter 100% below for payment 18
11111Year 1 Pmt 15.25%131250 431250 
12212Year 1 Pmt 25.25%131250 562500 
13313Year 1 Pmt 35.25%131250 693750 
14414Year 1 Pmt 45.25%13125082500033.00%82500000.00%
15521Year 2 Pmt 13.50%87500 912500 
16622Year 2 Pmt 23.50%87500100000040.00%1000000 
17723Year 2 Pmt 36.00%150000 1175000250001.00%
18824Year 2 Pmt 46.00%150000 1325000 
19931Year 3 Pmt 16.00%150000 1475000 
201032Year 3 Pmt 25.50%137500 1662500500002.00%
211133Year 3 Pmt 35.50%137500 1800000 
221234Year 3 Pmt 45.50%137500 1937500 
231341Year 4 Pmt 15.50%137500207500083.00%2075000 
241442Year 4 Pmt 23.40%85000 2160000 
251543Year 4 Pmt 33.40%85000 2245000 
261644Year 4 Pmt 43.40%85000 2330000 
271745Year 4 Pmt 53.40%85000 2415000 
281846Year 4 Pmt 63.40%850002500000100.00%2500000 
29         
30         
31         
32         
33         
34         
Mohamed_rev2
Cell Formulas
RangeFormula
H4H4=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")
H5H5=H3-H4
M3M3=$H$4*12/$J$4+($H$3-$H$4)*12/$J$5
M4M4=$H$4*12/$J$4
M5M5=($H$3-$H$4)*12/$J$5
E5E5=E3*E4
E6E6=E3-E5
H10H10=$E$5
I10I10="enter 100% below for payment "&M3
B11:B34B11=IFERROR(IF(OFFSET(B11,-1,0)+1<=$M$3,OFFSET(B11,-1,0)+1,""),"")
C11:C34C11=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:D34D11=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:E34E11=IF($B11="","",CONCATENATE("Year ", C11," Pmt ",D11))
F11:F34F11=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:G34G11=IF($B11="","",$E$3*F11)
K11:K34,H11:H34H11=IF($B11="","",IF(ISNUMBER(I11),$E$3*I11,""))
J11:J34J11=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
CellAllowCriteria
J4:J5List1,2,3,4,6,12
H3List2,3,4,5,6,7,8,9,10
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top