Caluclating value of Investment

[Cbrehm]

have the following columns with headers
A Date (this is the date bought
F penalty percent
G Total Paid
I Year 1 Taxes
J Year 2 Taxes
K Year 3 Taxes
L Year 4 Taxes
M Year 5 Taxes
O Date redeemed
S Value today

Rules
if Date redeemed is >0 do-not calculate
We buy on a date and are owed a 5% penalty immediately plus 1% per month till redeemed. ie buy for $100 in March, 2011 and date is now Nov,2011 we are owed 100+5 penalty + 8 interest for 8 months = $113.00
Each year in December we pay taxes for up to five years
We get 1% interest on this until redeemed
So, if we pay $10 in December of 2011 and is is now March of 2012 we are owed
$10 + 3% +100+5%+ 11% since it has now been 11 months since the purchase

Same thing year 2 thru 5

In other words we buy property and are owed a 5% penalty
1% per moth
Each year 1% per month on the new yearly tax payment from that date forward



Value today is calculated by the following

Total Paid * (1+ Penalty percent) +
TOTAL PAID *(1+ 1% PER MONTH from date +
1% per month on Year 1 thru 5 taxes from December of that year


I can do the formula but in 3 parts as it is longer that Excel can handle as one formula. There has to be an easy way.

Thanks

[day92]

There is alot of information here, I would recommend uploading a spreadsheet with sample data

[Cbrehm]

I just added the Spreadsheet