Forum Discussion
Deployement of amounts formulas
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:
- "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).
- "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.
- "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.