With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

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

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.

```
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

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

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.

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

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.

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

Thanks

Of should there be 5,000 taken per year?

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

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

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

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?

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.

All Courses

From novice to tech pro — start learning today.

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:

Open in new window

These are in the other attached workbook.Compounding--2-.xlsm

CD.xlsx