formula Logic

[Gimelie]

Say I want to create a sheet where I have a bunch of maturities in column A1 to A8 nominal amounts in B1 to B8 and all a year's months from C1 to N1. the formula im looking for would actually re-invest the nominal amount if the corresponding month surpassed that maturity. What im getting is that say if maturity is in march it's re-invested in april but also in may june july etc. All i want is that one reinvestment not redo it every single month!
Thanks :D

 

[baitmaster]

welcome to the board

your question isn't very clear, it helps if you can post your formulas, particularly if dealing with specialist financial products - the mere mention of maturities etc scares many people off, you'll get better responses if you can stick to actual Excel issues

In this case, I think you are asking why your formula, which I assume exists in a number of columns, returns results for every month greater than the month of maturity - as opposed to just that actual month

I am therefore guessing that you have an IF statement that tests if the column date is greater than the month of maturity. If this is the case, try narrowing your IF statement, to either (1) test that the months match exactly, or (2) that no value exists to the left of your current position

e.g. =IF(MONTH([column date]) = MONTH([maturity date]),[your formula],0)

If I'm wrong in my assumptions, please provide more details of your problem

hope this helps

 

[Gimelie]

well
Maturity Nominal Date
Jan11 BlueFeb11 March 11 April 11 May11
Red feb 11 500000 0 500000 0 0 0
march 11 1000000 0 0 1000000 0 0
march 11 500000 0 0 500000 0 0

This is practically it. Since my bond matures in Redfeb 11 the amount should be re-invested in Bluefeb 2011. it's the same thing for the 2 other cases in march.
what im doing is =if(BlueFeb11< Redfeb11, 500000,0) but excel automatically puts 500000 in everything after bluefeb11 which is logical, I just can't figure a formula that wouldn't do that. I wouldn't want to invest that amount EVERY single month after the date of maturity!
thanks!

 

[baitmaster]

still a bit difficult to read, but I can suggest a couple of things

1) not sure why you have redfeb and bluefeb, I would suggest you keep all dates as exactly that, dates, this allows you to do calculations on them. By including other data mixed in, you are changing them to text and Excel wont e.g. see february as a date

2) try using AND to return multiple criteria, or change the month as I suggested before

e.g. =IF(AND(bluefeb11 < Redfeb11 <REDFEB11,sum([range to left])=0),500000,0)

 

[Gimelie]

dude you are simply AWESOME! thanks loads !