i need and excel vba such that:
I have a loan ammortisation on excel, the interest rate is variable (euribor).This means that every 6 months the interest rate must be update with the relative euribor 6m value, available on the web.In order to check each time how much i paid really of interests and so on, i need to modify the relative rate (every 6 months) with the corresponding euribor.I can create a quesry from the web that has 10 rows, these rows show the euribor for 1 months period each, so e.g.
apr 2015 - 0.04%mar 2015 - 0.05%feb 2015 - 0.05%jen 2015 - 0.03%dec 2014 - 0.04%nov 2014 - 0.05%oct 2014 - 0.02%spt 2014 - 0.05%aug 2014 - 0.06%jul 2014 - 0.07%
this quesry shows the last 10 months euribor rates and auto updates, which means that in one month from now it would became:
may 2015 - 0.05%apr 2015 - 0.04%mar 2015 - 0.05%feb 2015 - 0.05%jen 2015 - 0.03%dec 2014 - 0.04%nov 2014 - 0.05%oct 2014 - 0.02%spt 2014 - 0.05%aug 2014 - 0.06%
So the query will Lose jul 2014 and update the first row with may 2015.
Suppose my loan has to refresh the euribor each jenuary and jul of the year. Suppose also that the loan start in 2014, so in jenuary 2014 the loan takes the corresponding value of the euribor in jenuary 2014 (for 6 payments) then in jul 2014 it takes the corresponding euribor for the next 6 payments, and so on. BUT of course hte problem is that once the loan gets the euribr for jenuary 2014 the 6 payments must contain that same euribor, and when it is jul these 6 payments (jen mar apr ...) do not have to change the euribor, only the next 6 payments ( july aug...) have to.
So here is my request:
i used this formula, IF(vlookup(..... exaplaining: if excel sees the jen 2014 date in the box, it has to copy the value int eh cell. Used this formula for the 6 cells corresponding to the 6 months; then in Jul 2014 in the cell i wrote the same formula but with jul 2014. If the date is not present in the box then they leave the past value of the euribor.
So june has the euribor of jenuary until the box shows june 2014.
The problem IS: every time the box updates, the last row disappear. So if the last raws was July 2014, then the cell loses the value. SO I NEED A MACRO that once the cell recognizes the value for june (for example) it copies and paste and cannot be changed anymore. So if when the box updates and cancel jul 2014, the cell will keep that value forever.
Of course i will show you everything with skype sharing the screen. It is Hard to be explained but really easy to show and understand.