# Coumpounding Value - CD

Hi Experts,

This question is Part 2 of my previous question. in that there is created two functions via VBA. to calculate compounding value of investment for x number of years. in this duration every year tax & some lump sum amount deducted from interest & produce final value of portfolio in one cell.

So now I need modification or totally new function to calculate say duration is 15 Years of investment of 100000 every year @ rate of interest 9% less 10% tax on interest earned less some lump sum amount.

This is the link of my previous question Part 1

Thank You
Compounding--1-.xlsm
LVL 8
###### Who is Participating?

Commented:
OK

I have corrected your calculations as they were not looking correct to me.
You had only 14 periods (not 15)
You had applied the tax reduction incorrectly.
The attached CD sheet is the correction.

I have finished the functions to do the two before and after tax calculations:
``````Function PFVdC(investmentValue As Double, investmentFequency As Long, APR As Double, numberYears As Long, Lump As Double, TaxRate As Double) As Double

'investmentValue        = principal paid at investmentFequency interval
'investmentFequency     = Number of years investmentValue is paid in

Dim n As Long 'number of periods
Dim r As Double 'percentage rate per period
Dim x As Long
Dim curValue As Double
Dim cinterest As Double

n = numberYears
r = APR
'curValue = investmentValue
For x = 1 To n
'calculate current investment
If x <= investmentFequency Then
're-add investment if less than investFrequency
curValue = curValue + investmentValue
End If
'calculate interest on current investment
cinterest = curValue * r
'reduce by Lump sum first
cinterest = cinterest - Lump
'reduce by tax on interest
cinterest = cinterest * (1 - TaxRate)
curValue = curValue + cinterest
Next x

PFVdC = curValue

End Function
Function PFVrC(investmentValue As Double, investmentFequency As Long, APR As Double, numberYears As Long, Lump As Double, TaxRate As Double) As Double

'investmentValue        = principal paid at investmentFequency interval
'investmentFequency     = Number of years investmentValue is paid in

Dim n As Long 'number of periods
Dim r As Double 'percentage rate per period
Dim x As Long
Dim curValue As Double
Dim cinterest As Double

n = numberYears
r = APR
'curValue = investmentValue
For x = 1 To n
'calculate current investment
If x <= investmentFequency Then
're-add investment if less than investFrequency
curValue = curValue + investmentValue
End If
'calculate interest on current investment
cinterest = curValue * r
'reduce by tax on interest
cinterest = cinterest * (1 - TaxRate)
'reduce by Lump sum
cinterest = cinterest - Lump
curValue = curValue + cinterest
Next x

PFVrC = curValue

End Function
``````
These are in the other attached workbook.
Compounding--2-.xlsm
CD.xlsx
0

Commented:
Could you provide a little more on the desired outcome of the function.

What would be the inputs?
What would be the result?
What simple steps are between the two?

If you can explain these three points I am sure we can provide the solution.

ATB
Steve.
0

Commented:
Two new functions (best guess from the infor provided):

``````Function PFVdC(princilpal As Double, APR As Double, numberYears As Long, AnnualFreq As Long, Lump As Double, TaxRate As Double) As Double

Dim n As Long 'number of periods
Dim r As Double 'percentage rate per period
Dim x As Long
Dim curValue As Double
Dim cinterest As Double

n = numberYears * AnnualFreq
r = APR / AnnualFreq
For y = 1 To n
curValue = princilpal / AnnualFreq
For x = 1 To y
cinterest = curValue * r
'Lump sum first
cinterest = cinterest - Lump
cinterest = cinterest * (1 - TaxRate)

curValue = curValue + cinterest
Next x
PFVdC = PFVdC + curValue
Next y

End Function
Function PFVrC(princilpal As Double, APR As Double, numberYears As Long, AnnualFreq As Long, Lump As Double, TaxRate As Double) As Double

Dim n As Long 'number of periods
Dim r As Double 'percentage rate per period
Dim x As Long
Dim curValue As Double
Dim cinterest As Double

n = numberYears * AnnualFreq
r = APR / AnnualFreq
For y = 1 To n
curValue = princilpal / AnnualFreq
For x = 1 To y
cinterest = curValue * r
'Rate first
cinterest = cinterest * (1 - TaxRate)
cinterest = cinterest - Lump

curValue = curValue + cinterest
Next x
PFVrC = PFVrC + curValue
Next y
End Function
``````

See attached workbook
Compounding--1-.xlsm
0

Sir Steve,

Give me 1 hour to check. I am sure  your guessing most probably right.

Thanks
0

Sir Steve,

i had checked & it seems perfect but if i change Cell E12 = 15 to 5 then it is giving wrong result. what is the meaning of 15 to 5?

investing 100000 annually for five year. maturity year is 15th year from very first investment year. so 1st 100000 is 15 year period of investment.2 nd 100000 is for 14 year.....& 5th investment 100000 is invested period of  10 year.
interest rate 9% lump withdrawal every year 5000 & Tax rate 10% on interest earned. compounding frequency annually i.e. 1

Thank You
0

Commented:
The 15 in Cell E12 is the number of years in total.
The formula did not take into account the need for seperate year figures for the total period investment and the number of annual investments of the principal.
So in its current format it is expecting the annual investments to be for the full life.

I can add a variable for the investment periods.
But how should it be handled if the return is monthly?
Would the investments be at the start of the year or divided by the number of periods in the year?

If you could provide clear description of how the formula should function I can code it.
Without this clear guidance I am rather left to a bit of guess work.

ATB
Steve.
0

ok Sir,

assume I have one investment product - Duration of 15 year - On 16th year I will get maturity amount.

in this product I have to pay 100K for 5 years.
Rate of interest 9%, tax rate 10% on interest earned and withdrawing 5000 annually.

so want to know what is the value of this product as on 16th year of maturity.

Thank You
0

Commented:
This is fine, I understand the difference, but not the boundaries of the function...

Will the 5 years of payments ever be every 6 months, 2 months, bi-annual?
what happens if the interest is not accrued annually but monthly?

I can make the function perform how you have specified, but there is some grey area around the full range of the function.
0

ok sorry forgot to mention ..my bad

so five years I have to pay 100K each year annually & compounding frequency yearly.

Thanks
0

Is that make my point clear Sir? or need more clarification ? I am not in hurry just saying.

Thanks
0

Commented:
Should you realy be taking out the lump sum for each 100,000 investment?
Of should there be 5,000 taken per year?

In your sum you would be taking a lump sum per 100,000 added.
0

I know it doesn't make sense to invest 100000 every year & withdraw 5000 every year instead of investing only 95000, but it as per tax planning & all have to go like this.

so every year till 5 year 100K paying . 15th year is maturity.
Add 9% compounding interest - frequency 1 per year. Till 15 year
Less 10% tax on interest earned. every year till 15 year
Less Lump 5000 every year till 15 year.

Thanks
0

Commented:
Yes, but by re-using the formula and then summing it, you get the wrong answer as you are taking a lump sum each year for each 100,000 you invest.

Surely this should be 5,000 per year... not 5,000 in year 1 and 10,000 in year 2 etc etc.
0

Yes it is 5000 every year till 15 (Fixed).

Thanks
0

Commented:
cool, so the last question...

does the investment always occur every year?
The formula allows for a number of interest payments n at the rate/n.
Would there be investments in periods n, or just years?
0

Sir I have done whole calculation in attached sheet. I had explain all in that.

Thanks
CD.xlsx
0

Sir The_Barman,

yes it is perfect & i feel my self dumb ....how do i make this silly mistake regarding calculation.

Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.