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]

Open excel select new.
In there there will be a template called loan amortization schedule.

That should do what you need. It will also show you the kind of formulae you need to use. So you should be able to either adapt the template or create one for yourself, using that one as a guide.

 

[mohamed ali abdelgawad]

Open excel select new.
In there there will be a template called loan amortization schedule.

That should do what you need. It will also show you the kind of formulae you need to use. So you should be able to either adapt the template or create one for yourself, using that one as a guide.

i find it and helps me in some things
except the below can see in example sheet i have amount to install in 10 years , every quarter i will pay amount.
but we have 3 conditions that every end of year i must pay minimum amount
in this way i did i will collect at the end of period 140 %
i want to destitute installment percent equally to collect only 100% at the end of the period.

	% per Q	Amount per Q	Minimum % collected at the end of year	Added %	Added amount
Year1 Q1​	3%​	25000​
Year1 Q2​	3%​	25000​
Year1 Q3​	3%​	25000​
Year1 Q4​	3%​	25000​	30%​	20%​	200000​
Year2 Q1​	3%​	25000​
Year2 Q2​	3%​	25000​
Year2 Q3​	3%​	25000​
Year2 Q4​	3%​	25000​	50%​	10%​	100000​
Year3 Q1​	3%​	25000​
Year3 Q2​	3%​	25000​
Year3 Q3​	3%​	25000​
Year3 Q4​	3%​	25000​	70%​	10%​	100000​
Year4 Q1​	3%​	25000​
Year4 Q2​	3%​	25000​
Year4 Q3​	3%​	25000​
Year4 Q4​	3%​	25000​
Year5 Q1​	3%​	25000​
Year5 Q2​	3%​	25000​
Year5 Q3​	3%​	25000​
Year5 Q4​	3%​	25000​
Year6 Q1​	3%​	25000​
Year6 Q2​	3%​	25000​
Year6 Q3​	3%​	25000​
Year6 Q4​	3%​	25000​
Year7 Q1​	3%​	25000​
Year7 Q2​	3%​	25000​
Year7 Q3​	3%​	25000​
Year7 Q4​	3%​	25000​
Year8 Q1​	3%​	25000​
Year8 Q2​	3%​	25000​
Year8 Q3​	3%​	25000​
Year8 Q4​	3%​	25000​
Year9 Q1​	3%​	25000​
Year9 Q2​	3%​	25000​
Year9 Q3​	3%​	25000​
Year9 Q4​	3%​	25000​
Year10 Q1​	3%​	25000​
Year10 Q2​	3%​	25000​
Year10 Q3​	3%​	25000​
Year10 Q4​	3%​	25000​

 

[mohamed ali abdelgawad]

anyone Help Me please

 

[CountTepes]

Unfortunately, not being an accountant, I cannot follow what you are trying to achieve.

From your table, I can see that you are paying or saving 1,000,000 in 40 instalments of 25,000

By making additional payments, you can either reduce the term, or the size of future payments.

Which are you trying to achieve and also are there interest debit or credit payments to take into account?

If this is money borrowed and repaid, then there will be interest charged. However if these are savings, then there will be interest which will accrue. Either way the final sum will always be greater than the principal. Remember that on loans and savings, interest is compounded.

If you are working with zero interest, then what you need to do, is to recalculate the minimum repayment for each month, based on the the remaining payment term and the amount left to pay.

 

[mohamed ali abdelgawad]

Unfortunately, not being an accountant, I cannot follow what you are trying to achieve.

From your table, I can see that you are paying or saving 1,000,000 in 40 instalments of 25,000

By making additional payments, you can either reduce the term, or the size of future payments.

Which are you trying to achieve and also are there interest debit or credit payments to take into account?

If this is money borrowed and repaid, then there will be interest charged. However if these are savings, then there will be interest which will accrue. Either way the final sum will always be greater than the principal. Remember that on loans and savings, interest is compounded.

If you are working with zero interest, then what you need to do, is to recalculate the minimum repayment for each month, based on the the remaining payment term and the amount left to pay.

thanks for your reply
yes i work with zero interest
all i want to do is when i change minimum percent ( D4 or D5 or D6) for end of period it automatically change installment percent per month (E9:E48) to achieve only 100% of the amount equally on this months after add additional amount at the end of period (E49:H49) .
my math cant help me in this calculation.

 

[CountTepes]

Try this putting this into the cells.

Make sure that any percentage cells are formatted as percent with 1 decimal place for a more accurate visibility.
C3 is copied down to row Year10 Q4 as are E3 and G3. D5 is copied to each Year Q4

 

[mohamed ali abdelgawad]

Try this putting this into the cells.

Make sure that any percentage cells are formatted as percent with 1 decimal place for a more accurate visibility.
C3 is copied down to row Year10 Q4 as are E3 and G3. D5 is copied to each Year Q4

thanks for your great help
but that not what i want to do

your calculate will change only the percent after every Added amount.
but this not what i want to do

i want to enter only D2:D6 on my example sheet and it automatically get the added percent at the end of period and redistribute percent equally for all installment months.
that because when i want to sell new unit i will till cst that he will pay with this way.

you can see sheet called "My Target" on example sheet.
it only added amount at the end of every year as per Minimum amount collected but all installment months are equally
how to do this ?

sorry if my English not very good to help me reach you exactly what i want

 

[CountTepes]

I'm just trying to understand.

When you add the amount at Q4, do you want to redistribute the amount to pay evenly over the whole term, or just the Q1-4 for that period?

If it is the whole term, then you need to have all the added amounts agreed in advance.

 

[mohamed ali abdelgawad]

do you want to redistribute the amount to pay evenly over the whole term

that's exactly what i want
when you do this you will reduce the amount for every payment
also i want to just add percent in each end of year.
like you see in "My Target" sheet