Solved

# Coumpounding Value - CD

Posted on 2014-03-10
152 Views
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
0
Question by:itjockey
• 9
• 8

LVL 24

Expert Comment

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

LVL 24

Expert Comment

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

LVL 8

Author Comment

Sir Steve,

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

Thanks
0

LVL 8

Author Comment

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

LVL 24

Expert Comment

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

LVL 8

Author Comment

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

LVL 24

Expert Comment

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

LVL 8

Author Comment

ok sorry forgot to mention ..my bad

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

Thanks
0

LVL 8

Author Comment

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

Thanks
0

LVL 24

Expert Comment

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

LVL 8

Author Comment

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

LVL 24

Expert Comment

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

LVL 8

Author Comment

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

Thanks
0

LVL 24

Expert Comment

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

LVL 8

Author Comment

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

Thanks
CD.xlsx
0

LVL 24

Accepted Solution

Steve earned 500 total points
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

LVL 8

Author Closing Comment

Sir The_Barman,

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

Thank You
0

## Featured Post

### Suggested Solutions

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…