Mar 23 2020 08:32 AM - edited Mar 23 2020 09:08 AM
I would much appreciate any help for the attached calculation. I cannot figure out an accurate formula for the bottom portion as those results depend on the values at the top.
Mar 23 2020 08:49 AM
Mar 23 2020 08:49 AM
@LT_Pgh Which part is missing a formula? Can you talk through what the calculation should do? I am not familiar with the basis points calculations.
Mar 23 2020 09:12 AM
The fee needs to be calculated on the total value of $520,000, but the foundation (45,000) must receive the lowest share of the fee.
Mar 23 2020 09:27 AM
@LT_Pgh You really aren't explaining this much. From what I can tell, you need the value in E16 to be the lowest bracket reached by the prior calculation; I have done something for that in the attached. If that isn't what you need, take some time to actually explain how the calculation works.
Mar 23 2020 09:48 AM
Sorry, I'm new at asking for help in this forum and I am not good at explaining. I have the correct formulas in the top section based on the schedule on the bottom left. In the bottom section of the calculation, I know that D17 needs to be 25,000,000 but I can't figure out a formula. If E5 (45,000,000)+D13(275,000,000)>C26(300,000,000), then D17 should be C26-D13 or 25,000,000, and E5-D17 (the difference needs to drop down to the next line as the totol of D13+D17 cannot be greater than 300,000,000. Does that make more sense? i've updated the spreadsheet with I hope more detail.
Mar 24 2020 04:13 AM
Solution@LT_Pgh I had a look at the calculator and thought about how I'd do it, and redesigned the calculation. The easiest way to do what you want is to calculate the fee for the whole estate, then the fee just for the family portion; the difference is the foundation fee. I also reworked the fee calculation into a single formula.
Mar 24 2020 05:36 AM
@Savia Thank you for your help! I don't know why I'm always trying to reinvent the wheel.
Mar 24 2020 04:13 AM
Solution@LT_Pgh I had a look at the calculator and thought about how I'd do it, and redesigned the calculation. The easiest way to do what you want is to calculate the fee for the whole estate, then the fee just for the family portion; the difference is the foundation fee. I also reworked the fee calculation into a single formula.