Forum Discussion

TheQuestionare's avatar
TheQuestionare
Copper Contributor
Jul 28, 2023

Deployement of amounts formulas

Hi,
I've made an calculator for quick analysis of investments and funding alternatives. Well, almost. 

No matter how hard I try, I just cant wrap my head around the formula to calculate the correlation of deployment of capital and use of loan.

 

For "Capitol Needed to deploy" I believe I got the correct formula, even though I don't know how:

MAX(B2;MIN(B5*0,15;IF(B3>B5*0,85;B2+B3-B5*0,85;B2)))

 

For "loan needed to deploy" I Just can't get it to work automatically as the sum of Capital + loan then becomes wrong..

 ABC
1Post£Comments
2Available capital         2 615 600 
3MAX bankloan         7 850 000 
4My budget      10 465 600 
5SUM of investment      10 196 500 
6   
7DEPLOYMENTS Criterias
8Capital needed to deploy?Minimum 15% of the total "SUM of investment", not exceeding 100% deployed, IF "MAX bankloan" is maxed then deploy more capital until available capital is maxed.
9Loan needed to deploy?Maximum 85% of "SUM of investment", Always keep as close to 85% as possible, do not exceed max bankloan
10Amount exceeding budget?Display the sum of remaining/missing funding from both capital and bankloan

 

PS. This is a made up example.


Any clever minds who can please help me out?

excel
Formulas and Functions
office 365

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 05, 2023

    TheQuestionare 

    To calculate the "Capital needed to deploy" and the "Loan needed to deploy" with the given criteria, you can use the following formulas in Excel:

    For "Capital needed to deploy" (cell B8):

    =MAX(B$2, MIN(B$5*0.15, IF(B$3>=B$5*0.85, B$2+(B$3-B$5*0.85), B$2)))

    For "Loan needed to deploy" (cell B9):

    =MIN(B$5*0.85, B$5-B$8)

    For "Amount exceeding budget" (cell B10):

    =B$4-B$5

    Explanation of the formulas:

    1. "Capital needed to deploy" (cell B8):
      • The formula checks whether the "Available capital" (cell B2) is greater than or equal to 15% of the "SUM of investment" (cell B5). If it is, then it uses the greater of the two: "Available capital" or 15% of the "SUM of investment."
      • If the "MAX bankloan" (cell B3) is greater than or equal to 85% of the "SUM of investment," it adds the amount exceeding 85% to the "Capital needed to deploy."
      • The formula ensures that the "Capital needed to deploy" does not exceed the "Available capital" (cell B2).
    2. "Loan needed to deploy" (cell B9):
      • The formula calculates the maximum 85% of the "SUM of investment" (cell B5).
      • It subtracts the "Capital needed to deploy" (cell B8) from the "SUM of investment" to get the remaining amount, which will be used as a loan if available.
    3. "Amount exceeding budget" (cell B10):
      • The formula calculates the difference between the "My budget" (cell B4) and the "SUM of investment" (cell B5), which represents the remaining/missing funding from both capital and bank loan.

    Make sure to adjust the cell references (e.g., B2, B3, B4, B5) in the formulas according to your actual worksheet layout. The formulas should automatically update when you input values in the relevant cells, and they will meet the criteria you described for deployment of capital and loan.

    The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

    Hope this will help you.

Resources