Macro to change formula

When there is data in ae2 formula in cell bn4 looks like this

=IF(YEAR($AE$2)<YEAR(AQ$1),BO4/((1+AQ4)^(BG4)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL4>AR$1,(IF($AE$2<AQ$1, (BO4/(1+AQ$2))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL4>AQ$1,(BO4/((1+AQ$2)^YEARFRAC(AQ$1,$AL4)))/((1+AP$2)^AQ$3),BO4/((1+AQ$2)^$AY4))),BO4/(1+AQ$2)^$AY4)))))))

This can be copied down and works fine till the time there is no further entry in column AE
However when new data is entered in Column AE, I need to change the formula in Column BN
If I copy above formula in BN4 to BN10, it would become

=IF(YEAR($AE$2)<YEAR(AQ$1),BO11/((1+AQ11)^(BG11)),(IF(YEAR($AE$2)>YEAR(AQ$1),0,(IF($AL11>AR$1,(IF($AE$2<AQ$1,(BO11/(1+AQ$2))/((1+AP$2)^AQ$3),BO11/((1+AQ$2)^AR$3))),(IF($AE$2<AQ$1,(IF($AL11>AQ$1,(BO11/((1+AQ$2)^YEARFRAC(AQ$1,$AL11)))/((1+AP$2)^AQ$3),BO11/((1+AQ$2)^$AY11))),BO11/(1+AQ$2)^$AY11)))))))

Whereas what I need is this

=IF(YEAR($AE$9)<YEAR(AQ$1),BO12/((1+AQ12)^(BG12)),(IF(YEAR($AE$9)>YEAR(AQ$1),0,(IF($AL12>AR$1,(IF($AE$9<AQ$1,(BO12/(1+AQ$2))/((1+AP$2)^AQ$10),BO12/((1+AQ$2)^AR$10))),(IF($AE$9<AQ$1,(IF($AL12>AQ$1,(BO12/((1+AQ$2)^YEARFRAC(AQ$1,$AL12)))/((1+AP$2)^AQ$10),BO12/((1+AQ$2)^$AY12))),BO12/(1+AQ$2)^$AY12)))))))
So what I m doing is I copy the formula from cell BN4 and paste in a word document, and then replace $AE$2 with $AE$9 and replace $3 with $10, so that I get my desired formula
And this would again work only till there is no further entry in column AE, as soon as new data is entered in column AE, I need to again change the formula in Bn, that is what I have been doing using Word find and replace command.
If you have any idea how a macro can be used in this situation, please help me. I thought of accomplishing this by using a macro which calls for input boxes, but I couldn’t do it. Any other suggestions, more than welcome!

Please see the attachment for more details.