Question on Formula for Available Funds

[CMJ]

Hi,

I'm trying to figure out the best way to automate the following financing / payment schedule, specifically the "Available Financing" column in bold below (based on a total number hard coded, in this case $500):

[TABLE="class: cms_table, width: 310"]
<tbody>[TR]
[TD="colspan: 2"]Payment Amount[/TD]
[TD="colspan: 2"]Percentage of Contract[/TD]
[TD]Available financing[/TD]
[TD]Required Client Payment[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$250.00[/TD]
[TD="colspan: 2"]25%[/TD]
[TD]$0.00[/TD]
[TD]$250.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$250.00[/TD]
[TD="colspan: 2"]25%[/TD]
[TD]$0.00[/TD]
[TD]$250.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$240.00[/TD]
[TD="colspan: 2"]24%[/TD]
[TD]$240.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$240.00[/TD]
[TD="colspan: 2"]24%[/TD]
[TD]$240.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$20.00[/TD]
[TD="colspan: 2"]2%[/TD]
[TD]$20.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD="colspan: 2"]$1,000.00[/TD]
[TD="colspan: 2"]100%[/TD]
[TD]$500.00[/TD]
[TD]$500.00[/TD]
[/TR]
</tbody>[/TABLE]



Essentially, if there is financing available for 50% of the project, I'd like that to automatically calculate at the END of the payment schedule - So in this scenario, financing available kicks in after the client has paid the $500, or 50% of the total value.

Any help would be much appreciated!

 

[Gerald Higgins]

Hi, this is probably do-able, but it's not clear to me exactly how you want this to work.

Can you explain exactly the logic you want to use in this calculation ?
Don't worry too much about explaining the real world application of this model, just focus on the data, and why you sometimes want to show 0, sometimes show the full payment amount, and sometimes show only part of the payment amount.

Are all the other columns already working correctly ?

 

[CMJ]

Hi, this is probably do-able, but it's not clear to me exactly how you want this to work.

Can you explain exactly the logic you want to use in this calculation ?
Don't worry too much about explaining the real world application of this model, just focus on the data, and why you sometimes want to show 0, sometimes show the full payment amount, and sometimes show only part of the payment amount.

Are all the other columns already working correctly ?

Thanks, Gerald. Yes, let me try to explain the logic.

I want a user to be able to input the total amount of that column (available financing), in this case $500. Based on total amount of payments (first column, $1,000), I know that the difference between available financing and payments required is $500. I need the "Required Client Payments" to total this difference before the "Available Financing" column kicks in. Essentially, any payments in the "Required Client Payments" need to precede the "Available Financing" payments. I hope I'm explaining this in enough detail, but it's difficult to verbalize without getting into the particulars of the real world application.

Currently, the "Required Client Payments" column is a simple =(A2-C2) type of formula, which is why I was focusing on the "Available Financing" column, but I'm open to suggestions on how to best adjust this.

Thanks for your help!

 

[Marcelo Branco]

Not sure i understand what you are looking for

See if this does what you need (a simulation with input in C7 = 600)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Payment Amount​

[/td][td]

Percentage of Contract​

[/td][td]

Available financing​

[/td][td]

Required Client Payment​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

250,00​

[/td][td]

25%​

[/td][td]

0,00​

[/td][td]

250,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

250,00​

[/td][td]

25%​

[/td][td]

100,00​

[/td][td]

150,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

240,00​

[/td][td]

24%​

[/td][td]

240,00​

[/td][td]

0,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

240,00​

[/td][td]

24%​

[/td][td]

240,00​

[/td][td]

0,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

20,00​

[/td][td]

2%​

[/td][td]

20,00​

[/td][td]

0,00​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1000,00​

[/td][td]

100%​

[/td][td]

600,00​

[/td][td]

400,00​

[/td][/tr]
[/table]

Formula in C2 copied down
=MAX(0,MIN(A2,C$7+A2-SUM(A2:A$6)))

Formula in D2 copied down
=A2-C2

Hope this helps

M.

 

[CMJ]

Not sure i understand what you are looking for

See if this does what you need (a simulation with input in C7 = 600)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Payment Amount​

[/TD]
[TD]

Percentage of Contract​

[/TD]
[TD]

Available financing​

[/TD]
[TD]

Required Client Payment​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

250,00​

[/TD]
[TD]

25%​

[/TD]
[TD]

0,00​

[/TD]
[TD]

250,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

250,00​

[/TD]
[TD]

25%​

[/TD]
[TD]

100,00​

[/TD]
[TD]

150,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

240,00​

[/TD]
[TD]

24%​

[/TD]
[TD]

240,00​

[/TD]
[TD]

0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

240,00​

[/TD]
[TD]

24%​

[/TD]
[TD]

240,00​

[/TD]
[TD]

0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

20,00​

[/TD]
[TD]

2%​

[/TD]
[TD]

20,00​

[/TD]
[TD]

0,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

1000,00​

[/TD]
[TD]

100%​

[/TD]
[TD]

600,00​

[/TD]
[TD]

400,00​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=MAX(0,MIN(A2,C$7+A2-SUM(A2:A$6)))

Formula in D2 copied down
=A2-C2

Hope this helps

M.

Thank you so much! That works perfectly. Have a wonderful day!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.