I am making a retirement calculator of sorts where a user inputs their age, rate, expected growth, etc. I am having trouble getting the rate of return to multiple against the previous years balance. I believe the correct answer would look like this in excel:
However, when trying in qlik sense I get the following:
the first column includes the 8% annual return, and shows that the initial contribution matches. The Second doesnt include it, but does not match after the initial contribution obviously. Here are the respective formulas. I am using variable sliders to let the user control:
I also can't see a way of applying the growth to the prior periods correctly, either through accumulation or above/rangesum.
I have looked trying a UDF, that you pass row number raise and return, however I don't seem to be able to use either looping or recursion, therefore this is a bit of a brute force attempt.
I've done for 5 years, you would expand the range of years by adding in extra rows of formulae inbetween row 31/32, they are going to get long and messy (could potentially use Excel to write out the formulae & copy paste in), but I think this is possible & your range of years should have a natural limt (?), but I don't know if Qlik will break before you get to that;
I've also not looked at your current value and age sliders or how they are incorporated (or in fact used sliders at all), but I think that should be simpler, if this bit works, which (for my 5 year sample), looks like it might;
Attached the QVF, do post back if this works or if you find a more elegant solution.