Forum Discussion

himanshu_nassa's avatar
himanshu_nassa
Copper Contributor
Sep 15, 2020

CarlisleSol 

 

Per my understanding, you need to build a stream of cash flows going out in the future which would have a series of dates and you need to figure out which months should have investment and which months should not. Let me know if the understanding is different.

 

I assume you have dates on the top of your table for which we would determine if the investment should happen or not. It can be done by using the MOD function and checking the result with divisor 2 for getting payments in alternate months.

 

=IF(MOD(MONTH(L$1)-MONTH($F3), 2)=0, "Invest", 0)

 

=IF(MOD(MONTH(Date of the period which needs to be tested)-MONTH(date of the first cash flow), 2)=0, Investment Amount , 0)

 

I have added the formula for one of the records in your spreadsheet. See attached.

 

You would replace the "Invest" in the formula with the amount you want to be invested.

 

Please let me know if this is the required result or else please explain the issue further.

 

If this gives you the required result, please vote and mark this post.

CarlisleSol's avatar
CarlisleSol
Copper Contributor
Oct 12, 2020

himanshu_nassa 

 

Good Afternoon 

Thank you for your previous assistance. I have a attached my attempt at using your formula, for some reason my investments are being dispersed in the incorrect months. Kindly see excel sheet attached, I have placed notes in order to provide some guidance.

 

Could you kindly advise what I am doing incorrectly in my formula.

 

Your assistance will be appreciated.

 

Kind REgards,

Carlisle Solomon 

  • himanshu_nassa's avatar
    himanshu_nassa
    Copper Contributor
    Oct 18, 2020

    CarlisleSol - The formula in H3 is comparing the current date with End date instead of the Start Date.

     

    Check out the attached file with the correction.

     

    Thanks,

    Himanshu

    http://sipnlearn.in

     

     

Resources