Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
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
These are in the other attached workbook.
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.