Solved

Coumpounding Value - CD

Posted on 2014-03-10
17
152 Views
Last Modified: 2014-03-12
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
Comment
Question by:itjockey
  • 9
  • 8
17 Comments
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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

by:Steve
Comment Utility
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

Open in new window


See attached workbook
Compounding--1-.xlsm
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
Sir Steve,

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

Thanks
0
 
LVL 8

Author Comment

by:itjockey
Comment Utility
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

by:Steve
Comment Utility
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

by:itjockey
Comment Utility
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

by:Steve
Comment Utility
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

by:itjockey
Comment Utility
ok sorry forgot to mention ..my bad

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


Thanks
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Author Comment

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


Thanks
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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

by:itjockey
Comment Utility
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

by:Steve
Comment Utility
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

by:itjockey
Comment Utility
Yes it is 5000 every year till 15 (Fixed).

Thanks
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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

by:itjockey
Comment Utility
Sir I have done whole calculation in attached sheet. I had explain all in that.


Thanks
CD.xlsx
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
Comment Utility
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)
'add interest back in
        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
'add interest back in
        curValue = curValue + cinterest
    Next x

PFVrC = curValue

End Function

Open in new window

These are in the other attached workbook.
Compounding--2-.xlsm
CD.xlsx
0
 
LVL 8

Author Closing Comment

by:itjockey
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now