Solved

Coumpounding Value - CD

Posted on 2014-03-10
17
157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39917822
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
ID: 39917856
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
ID: 39917993
Sir Steve,

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

Thanks
0
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 8

Author Comment

by:itjockey
ID: 39918180
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
ID: 39919830
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
ID: 39919846
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
ID: 39919944
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
ID: 39920041
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

by:itjockey
ID: 39920237
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
ID: 39920728
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
ID: 39920775
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
ID: 39921198
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
ID: 39921335
Yes it is 5000 every year till 15 (Fixed).

Thanks
0
 
LVL 24

Expert Comment

by:Steve
ID: 39921671
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
ID: 39922597
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
ID: 39923535
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
ID: 39924334
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

733 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