Naresh Patel
asked on
Coumpounding Value
Hi Experts,
as I know how to calculate compounded interest on investment & get portfolio value in excel .
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
as I know how to calculate compounded interest on investment & get portfolio value in excel .
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
ASKER
Sir.The_Barman,
I am on way back to home.let me reach home and I will get back to you.
Thanks
I am on way back to home.let me reach home and I will get back to you.
Thanks
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
Attached is some workings I have done so far.
Book1.xlsx
ASKER
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
Thanks
ASKER
Oops posting from mobile so don't see your comment.
Thanks
Thanks
I have no doubt it can be done.
It is just not simple.
It is just not simple.
ASKER
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
i will ask separate question for that criteria.
Thanaks
ASKER
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
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
ASKER
are you with me sir?
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.
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.
ASKER
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
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
ASKER
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.it Is ok. I thought it will appear like defaults function.
Is this critical for you? (to have the arguments appear like an in-built function)
Thanks
ASKER
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
Thanks
ASKER
Sir Steve,
This is part 2 & last part of this question series. will pls look in to this Part 2
Thanks
This is part 2 & last part of this question series. will pls look in to this Part 2
Thanks
The Tax is easy enough as it just reduces the Interest rate (so 9% becomes 8.1%)
=D2*((1+((D3*(1-D7))/D4))^
But the Deduction as a fixed value before or after tax, you may need to go line by line to calculate the net effect.