Calculate capitalised interest, complicated issue for the Ultimate Excel Champion (UEC)

[cafepassione]

Hi
Thanks for your help in advance. (I am completely defeated), please see attached (with detailed description of helps required). I will be completely speechless and extremely grateful if anyone can help on this! :-)

Solution looking for
1. an automated calculation that can calcualte YTD and monthly amount by just changing the reporting month ( see attached).

Criteria
1. No marco is allowed.
2. CAPEX data download is YTD only.

Background
Try to calculate produce a schedule to automate the calculation of capitalised interest.
Base on the reporting month choose, one of the function is to calculate the YTD and monthly capitalised interest for an asset.

Capitalised interest needs to calculate as follow:

Say if now is Apr 12, the asset value base for calculating capitalised interest should be:

Asset value (project to date) in Apr 12 = closing bal. of prior years + Apr 12 monthly CAPEX (variable) + the YTD CAPEX (Jan to Mar 12) + the capitalised interest calculated in Jan 12 [base on the asset value (project to date) in Jan 12, which is prior year bal + jan 12 monthly capex]; + capitalisd interest calculated in Feb 12 [based on the asset value (project to date) in Feb 12, which is the asset value (project to date) in Jan 12 + feb 12 monthly capex + the capitalised interest portion done in Jan 12] + Mar 12 (same logic applies, again, if this is not clear, just open the attached, it explains most of the things.).

This is complicated to describe here, but once you see the "red" color highlighted area in the attached. it is much simplier, i am thinking is compounding interest or some financial formula is the way to go? i appreciate if someone can edit that and provide me a solution.

Thanks for your help!!!!!!!!!!!!!!!!! :-)

[darknation144]

Look into =accrint() or =accrintm()

[cafepassione]

Look into =accrint() or =accrintm()

Thanks for your help. But I guess it wouldn't works as there is a variable contributions. Regards.