Solved

Coumpounding Value

Posted on 2014-03-04
17
221 Views
Last Modified: 2014-03-10
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
0
Comment
Question by:itjockey
  • 11
  • 6
17 Comments
 
LVL 24

Expert Comment

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

Author Comment

by:itjockey
Comment Utility
Sir.The_Barman,

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

Thanks
0
 
LVL 24

Expert Comment

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

Author Comment

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

Author Comment

by:itjockey
Comment Utility
Oops posting from mobile so don't see your comment.

Thanks
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
I have no doubt it can be done.
It is just not simple.
0
 
LVL 8

Author Comment

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

Author Comment

by:itjockey
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Author Comment

by:itjockey
Comment Utility
are you with me sir?
0
 
LVL 24

Expert Comment

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

Author Comment

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

Accepted Solution

by:
Steve earned 500 total points
Comment Utility
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
 
LVL 8

Author Comment

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

Expert Comment

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

Author Comment

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

Author Closing Comment

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

Author Comment

by:itjockey
Comment Utility
Sir Steve,

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

Thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

10 Experts available now in Live!

Get 1:1 Help Now