Multiple If statements, Calculating quarterly % holding costs
 

Hi Guys/Girls

I am trying to assign %'s for a Property Holding Cost. I cannot formulate the correct IF statements to correctly calculate the below-mentioned equation (See attached document).

1 2 3 4 5 6 7 8 9 10 Time Period (Months)
10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Cumulative Cost % assignment
10% 10% 10% 10% 10% 10% 10% 10% 10% 10% Spread Cost % assignment

The cost needs to be paid quarterly, i.e. month cost % 1,2&3 are totaled in Month 3(i.e. 30%). When the property is sold, the remaining 10th month holding cost is totaled to finalize the Sale (i.e. 10%).

Example: Holding Cost is $12,000, paid quarterly, the Property is sold at the end of the 10th Month.

i.e. Time Period 1-0%($0), 2-0%($0), 3-30%($3,600), 4-0%($0), 5-0%($0), 6-30%($3,600), 7-0%($0), 8-0%($0), 9-30%($3,600), 10 -Property is sold Remaining 10%($1,200).

See attached Excel: Row 17 is highlighted in yellow, the correct equation must be able to be filled across.

I seem to be able to get it, then I change the month and it exposes the formula. If you could change the holding Month Period to make sure it works, I would be most appreciative.

Thanks to anyone that can help me here

Kind Regards,

Mitchell