Coumpounding Value

Hi Experts,

as I know how to calculate compounded  interest on  investment & get portfolio value  in excel .Coumpounded Interest Value
But is there any way to deduct Tax percentage on interest earn every year & deduct some lump sum amount from interest every year.

There will be two situation - Deduct lump sum amount & then calculate tax % every year

& tax % & then deduct lump sum amount.

Thanks
LVL 8
Naresh PatelTraderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SteveConnect With a Mentor Commented:
OK, the code in the attached workbook is below:

Function PFVd(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
curValue = princilpal

For x = 1 To n
    cinterest = curValue * r
    'Lump sum first
    cinterest = cinterest - Lump
    cinterest = cinterest * (1 - TaxRate)
    
    curValue = curValue + cinterest
Next x
PFVd = curValue

End Function

Function PFVr(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
curValue = princilpal

For x = 1 To n
    cinterest = curValue * r
    'Rate first
    cinterest = cinterest * (1 - TaxRate)
    cinterest = cinterest - Lump
    
    curValue = curValue + cinterest
Next x
PFVr = curValue

End Function

Open in new window


The reasons for two functions is that the function would not know the difference between a Lump and a Tax rate. So it is easier to remove all ambiguity and create two functions.

See attached workbook and ask if you need more info.
Compounding.xlsm
0
 
SteveCommented:
I am not sure there is a simple formula which can be applied to take into account the deductions...

The Tax is easy enough as it just reduces the Interest rate (so 9% becomes 8.1%)
=D2*((1+((D3*(1-D7))/D4))^(D4*D5)) where D7 is the Tax.

But the Deduction as a fixed value before or after tax, you may need to go line by line to calculate the net effect.
0
 
Naresh PatelTraderAuthor Commented:
Sir.The_Barman,

I am on way back to home.let me reach home and I will get back to you.

Thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
SteveCommented:
Cool, I have looked at the posibility of a single formula, but I do not think it will be simple.

Attached is some workings I have done so far.
Book1.xlsx
0
 
Naresh PatelTraderAuthor Commented:
But Sir but  your formula for converting amount in to words ( crore and all ) which is I guess still open. In that you have produced such a very big formula.it can't happen to this calculations?

Thanks
0
 
Naresh PatelTraderAuthor Commented:
Oops posting from mobile so don't see your comment.

Thanks
0
 
SteveCommented:
I have no doubt it can be done.
It is just not simple.
0
 
Naresh PatelTraderAuthor Commented:
i had seen your calculation, yes it is perfect.....but if it is in one formula then it will be awesome. if it is possible then i will wait take your time. my next question is relate to this only, this is strait 100000 investment & 15 year term period, just need to know if i add up every year 100000 till 15 year. what will be the total value of investment. tax rate is same & lump sum amount is same.

i will ask separate question for that criteria.

Thanaks
0
 
Naresh PatelTraderAuthor Commented:
How bat code instead of formula. Then it is possible in one cell? I am talking about Function.
like this =PFV(Principle amount, interest rate, compounding frequency for year, Tax Rate, Lump Sum, investment Period) for Tax first then lump sum.

=PFV(Principle amount, interest rate, compounding frequency for year, Lump Sum, Tax Rate, investment Period)


Thanks
0
 
Naresh PatelTraderAuthor Commented:
are you with me sir?
0
 
SteveCommented:
VBA would be far easier to do...

I will create two functions for you...

PFVt for tax first
PFVd for decreasing lump first.

I will try to get these done over this weekend.

ATB
Steve.
0
 
Naresh PatelTraderAuthor Commented:
Okie. Happy to hear that.but it can't be only one function "PFV" ? and manage lump and tax in its range order as described in my previous post. Just asking do as per your convenience.


Thank you
0
 
Naresh PatelTraderAuthor Commented:
Yes it is perfect, only one thing-  it is possible? when dealing with this two functions there possible to see function argument below same as in we see in default function of MS Excel?

i.e.I mean to say this
0
 
SteveCommented:
It is possible (I believe) if the UDF is registered using VBA.
You can simply press the function symbol to the left of the formula area, this will bring up a dialog box to aid in filling the function.
Is this critical for you? (to have the arguments appear like an in-built function)
0
 
Naresh PatelTraderAuthor Commented:
It is possible (I believe) if the UDF is registered using VBA.

I dint understand
You can simply press the function symbol to the left of the formula area, this will bring up a dialog box to aid in filling the function.
Is this critical for you? (to have the arguments appear like an in-built function)
it Is ok. I thought it will appear like defaults function.

Thanks
0
 
Naresh PatelTraderAuthor Commented:
As usual perfect. only one thing I dint get your last post. via Fx menu I can see arguments but if I type directly in cell I dint see function arguments as MS Excel defaults functions have.


Thanks
0
 
Naresh PatelTraderAuthor Commented:
Sir Steve,

This is part 2 & last part of this question series. will pls look in to this Part 2

Thanks
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.

All Courses

From novice to tech pro — start learning today.