CAGR advanced help with possible VBA needed

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3142898%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECAGR%20advanced%20help%20with%20possible%20VBA%20needed%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3142898%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20on%20a%20piece%20of%20work%20I%20am%20completing%20using%20a%20Compound%20Annual%20Growth%20rate%20calulator.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20excel%20I%20would%20like%20to%20be%20able%20to%20work%20our%20the%20following%3A-%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E1)%20Given%20that%20I%20have%20the%20end%20value%20(B10)%2C%20and%20I%20have%20the%20exisitng%20providers%20end%20value%20(B12)%2C%20I%20would%20like%20to%20calculate%20the%20CAGR%20required%20to%20make%20my%20fund%20value%20match%20the%20exisiting%20providers.%20I%20know%20the%20answer%20should%20be%20i.r.o%203.03%25%20but%20need%20help%20to%20work%20it%20out.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E2)%20I%20would%20like%20the%20cells%20G3%20-%20P139%20to%20be%20completed%20automatically%20given%20the%20inputs%20in%20column%20B%20are%20entered.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ERob%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3142898%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EMacros%20and%20VBA%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Contributor

Hi,

 

I am having trouble on a piece of work I am completing using a Compound Annual Growth rate calulator.

 

In the attached excel I would like to be able to work our the following:-

 

1) Given that I have the end value (B10), and I have the exisitng providers end value (B12), I would like to calculate the CAGR required to make my fund value match the exisiting providers. I know the answer should be i.r.o 3.03% but need help to work it out.

 

2) I would like the cells G3 - P139 to be completed automatically given the inputs in column B are entered.

 

Many thanks in advance,

 

Rob

3 Replies

@Robert1290 Haven't really tried to come up with a formula that calculates the required growth rate, but your schedule has all the calculations in it. What's left is to use Goal Seek to get the desired ending value.

This results in a growth rate of 3,008214793%, to be precise. Wouldn't that work for you?

Riny_van_Eekelen_0-1644415985254.png

 

@Riny_van_Eekelen

Many thanks for trying on this, it seems to help me get past one hurdle, much appreciated. However, I would like to still try and get a formula working, and also help on my second question.
@Riny_van_Eekelen

I have also tried adjusting the projected growth rate down by 0.5%, which gives me a lower end value but the same percentage on goal seek which is not what I am trying to acheive. I think a formula is the only way to go.