Help on Financial formula

Here you go

Interest compounded monthly
Decay Calculated yearly

goalAmt = 1000000
PayAmt = 10
Do While newAmt < goalAmt
  tmpAmt = tmpAmt + PayAmt
  prevAmt = 0
  prinAmt = 0
  DecAmt = 0
  intAmt = 0
  For a = 1 To 240
    prevAmt = prevAmt + tmpAmt
    prinAmt = prinAmt + tmpAmt
   
    tintAmt = prevAmt * (0.14 / 12)
    intAmt = intAmt + tintAmt
   
    prevAmt = prevAmt + tintAmt
    If a Mod 12 = 0 Then
      tDecAmt = prevAmt * 0.08
      DecAmt = DecAmt + tDecAmt
      prevAmt = prevAmt - tDecAmt
    End If
  Next a
  newAmt = prevAmt
Loop
'intAmt
MsgBox "Monthly Payment = " & Format(tmpAmt, "$#.00") & Chr(13) & _
       "Principal = " & Format(prinAmt, "$#.00") & Chr(13) & _
       "Interest = " & Format(intAmt, "$#.00") & Chr(13) & _
       "Decay = " & Format(DecAmt, "$#.00") & Chr(13) & _
       "Total = " & Format(newAmt, "$#.00")
       

a.) The total amount exceed the goal amount.

b.) The 10 you entered as a start value.... What is it. Removing it though kill the machine in processing something.

Here is your new form with the code in the click event for the Compute button.  The new Text boxes are required to give a starting point for the calculation.

In the previous code the value 10 was the incremental value of the monthly savings if the calculation proved to be invalid.


VERSION 5.00
Begin VB.Form Form1
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "Goal saving"
   ClientHeight    =   5184
   ClientLeft      =   48
   ClientTop       =   336
   ClientWidth     =   6108
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   5184
   ScaleWidth      =   6108
   StartUpPosition =   2  'CenterScreen
   Begin VB.Frame Frame2
      Caption         =   "Starting Points for Calculations"
      Height          =   912
      Left            =   216
      TabIndex        =   21
      Top             =   4068
      Width           =   5628
      Begin VB.TextBox Txt_New
         Height          =   252
         Index           =   1
         Left            =   2340
         TabIndex        =   25
         Top             =   432
         Width           =   948
      End
      Begin VB.TextBox Txt_New
         Height          =   252
         Index           =   0
         Left            =   2340
         TabIndex        =   24
         Top             =   180
         Width           =   948
      End
      Begin VB.Label Lbl_New
         Caption         =   "Savings Increment Amount"
         Height          =   192
         Index           =   1
         Left            =   108
         TabIndex        =   23
         Top             =   468
         Width           =   2028
      End
      Begin VB.Label Lbl_New
         Caption         =   "Savings Start Amount"
         Height          =   192
         Index           =   0
         Left            =   108
         TabIndex        =   22
         Top             =   216
         Width           =   1740
      End
   End
   Begin VB.Frame Frame1
      Height          =   3435
      Left            =   180
      TabIndex        =   10
      Top             =   60
      Width           =   5715
      Begin VB.TextBox txtTotal
         BackColor       =   &H00C0FFFF&
         Enabled         =   0   'False
         Height          =   285
         Left            =   4200
         TabIndex        =   20
         Text            =   "0.00"
         Top             =   2940
         Width           =   1395
      End
      Begin VB.TextBox txtLoss
         BackColor       =   &H00C0FFFF&
         Enabled         =   0   'False
         Height          =   285
         Left            =   4200
         TabIndex        =   18
         Text            =   "0.00"
         Top             =   2640
         Width           =   1395
      End
      Begin VB.TextBox txtInterest
         BackColor       =   &H00C0FFFF&
         Enabled         =   0   'False
         Height          =   285
         Left            =   4200
         TabIndex        =   17
         Text            =   "0.00"
         Top             =   2340
         Width           =   1395
      End
      Begin VB.TextBox txtPrincipal
         BackColor       =   &H00C0FFFF&
         Enabled         =   0   'False
         Height          =   285
         Left            =   4200
         TabIndex        =   16
         Text            =   "0.00"
         Top             =   2040
         Width           =   1395
      End
      Begin VB.TextBox txtMortgage
         BackColor       =   &H00C0FFFF&
         Enabled         =   0   'False
         Height          =   285
         Left            =   4200
         TabIndex        =   15
         Text            =   "0.00"
         Top             =   1740
         Width           =   1395
      End
      Begin VB.TextBox txtInf
         Height          =   285
         Left            =   4200
         TabIndex        =   7
         Text            =   "0.000"
         Top             =   1200
         Width           =   1395
      End
      Begin VB.TextBox txtLength
         Height          =   285
         Left            =   4200
         TabIndex        =   5
         Text            =   "0"
         Top             =   900
         Width           =   1395
      End
      Begin VB.TextBox txtAPR
         Height          =   285
         Left            =   4200
         TabIndex        =   3
         Text            =   "0.000"
         Top             =   600
         Width           =   1395
      End
      Begin VB.TextBox txtGoal
         Height          =   285
         Left            =   4200
         TabIndex        =   1
         Text            =   "0.00"
         Top             =   300
         Width           =   1395
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Total"
         Height          =   255
         Index           =   8
         Left            =   180
         TabIndex        =   19
         Top             =   2955
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Loss due to inflation."
         Height          =   255
         Index           =   7
         Left            =   180
         TabIndex        =   14
         Top             =   2655
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Interest amount."
         Height          =   255
         Index           =   6
         Left            =   180
         TabIndex        =   13
         Top             =   2355
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Principal amount."
         Height          =   255
         Index           =   5
         Left            =   180
         TabIndex        =   12
         Top             =   2055
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Monthly savings amount."
         Height          =   255
         Index           =   4
         Left            =   180
         TabIndex        =   11
         Top             =   1740
         Width           =   2775
      End
      Begin VB.Line Line1
         X1              =   5580
         X2              =   120
         Y1              =   1620
         Y2              =   1620
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Decay in capital (Inflation)."
         Height          =   255
         Index           =   3
         Left            =   180
         TabIndex        =   6
         Top             =   1215
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "Length in months to reach the goal."
         Height          =   255
         Index           =   2
         Left            =   180
         TabIndex        =   4
         Top             =   915
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "APR"
         Height          =   255
         Index           =   1
         Left            =   180
         TabIndex        =   2
         Top             =   615
         Width           =   2775
      End
      Begin VB.Label lblGen
         BackStyle       =   0  'Transparent
         Caption         =   "GOAL amount to be reached."
         Height          =   255
         Index           =   0
         Left            =   180
         TabIndex        =   0
         Top             =   300
         Width           =   2775
      End
   End
   Begin VB.CommandButton cmdCompute
      Caption         =   "Com&pute"
      Height          =   435
      Left            =   3180
      TabIndex        =   9
      Top             =   3600
      Width           =   1335
   End
   Begin VB.CommandButton cmdClear
      Caption         =   "&Clear"
      Height          =   435
      Left            =   4560
      TabIndex        =   8
      Top             =   3600
      Width           =   1335
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Option Explicit

Private Sub cmdClear_Click()
txtGoal.Text = "0.00"
txtAPR.Text = "0.000"
txtLength.Text = "0"
txtInf.Text = "0.000"

txtMortgage.Text = "0.00"
txtPrincipal.Text = "0.00"
txtInterest.Text = "0.00"
txtLoss.Text = "0.00"
txtTotal.Text = "0.00"

txtGoal.SetFocus
End Sub

Private Sub cmdCompute_Click()

' 'type mismatch' error caused by empty Text Box
' So leading zeros will change nothing
If CDbl("0" & Txt_New(0).Text) = 0 Then Txt_New(0).Text = "100.00"
If CDbl("0" & Txt_New(1).Text) = 0 Then Txt_New(1).Text = "0.10"
Screen.MousePointer = 11
goalAmt = CDbl(txtGoal.Text)
tmpAmt = CDbl(Txt_New(0).Text)
PayAmt = CDbl(Txt_New(1).Text)
InterestPer = CDbl(txtAPR.Text)
NoMonths = CDbl(txtLength.Text)
Inflation = CDbl(txtInf.Text)

Do While newAmt < goalAmt
  tmpAmt = tmpAmt + PayAmt
  prevAmt = 0
  prinAmt = 0
  DecAmt = 0
  intAmt = 0
  For a = 1 To 240
    prevAmt = prevAmt + tmpAmt
    prinAmt = prinAmt + tmpAmt
   
    tintAmt = prevAmt * (InterestPer / 12)
    intAmt = intAmt + tintAmt
   
    prevAmt = prevAmt + tintAmt
    If a Mod 12 = 0 Then
      tDecAmt = prevAmt * 0.08
      DecAmt = DecAmt + tDecAmt
      prevAmt = prevAmt - tDecAmt
    End If
  Next a
  newAmt = prevAmt
Loop
'intAmt
txtMortgage.Text = Format(tmpAmt, "$#,###.00")
txtPrincipal.Text = Format(prinAmt, "$#,###.00")
txtInterest.Text = Format(intAmt, "$#,###.00")
txtLoss.Text = Format(DecAmt, "$#,###.00")
txtTotal.Text = Format(newAmt, "$#,###.00")
Screen.MousePointer = 0

End Sub

To Hornet241

Sorry for taking so long. I was on leave and hope to answer you shortly!

BTW, thanks

To Hornet241

Hi there,

To ponder ....

  dAPR = (APR / 1200)

' Give me the monthly Payment
  dMontlyPayment = ((Amount - Saved) * dAPR) / ((1 + dAPR) ^ Length - 1)
 
' Give me the total Contribution
  dTotPayment = dMontlyPayment * ((1 + dAPR) ^ Length - 1) / ((1 + dAPR) - 1)


' ? Interest
' ? Inflation

Was there a problem with what I gave you previously?

The calculations you present have too many unknown variables:

 dMontlyPayment = ((Amount - Saved) * dAPR) / ((1 + dAPR) ^ Length - 1)

dMontlyPayment -> Unknown
Amount         -> Known
Saved          -> Unknown
dAPR           -> Known

How can you complete this calculation with two different unknown variables?  Trig, Calculus???

To Hornet241

Sorry for the misunderstanding and I hope to have it sorted out right now.

I would like to send the project and forms to you if you are willing to post you address. The final result will be posted for all to see.

BTW - Thanks for the help thus far.

Send it to hornet241@hotmail.com

To Hornet241, I've send the code.

beneke, have you already solved this or it is yet an open question. I think I may help, but I need more info what exactly you want to calculate.
E.g.:
"I have a loan that is for $1000000. Loans Annual Rate is 14%. Loan is due in 240 months. Monthly payments, Compound interest (or Simple interest?).
I want to make a deposit from which to redeem the loan.
What should be my investment, with what interest rate?"

Note that inflation have influence on both loans and deposits.

So I will be glad to help (if you still need to)

Hi beneke,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Hornet241's comment(s) as an answer.

beneke, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Hi zlatev, still wan't to help out here.

beneke, rephrase your question - just tell what data you have, and what you want to achieve.

Hi there,
Here it goes,

I want a user to enter a Goal amount to be reached over a certain period of time taken inflation(decay in capital) and interest capitilized into account.

The result should tell me in a table:
a) what the user should save each month to reach the goal
b) the interest earned for the month
c) the total interest earned
d) loss due to inflation for the month
e) total inflation

The montlhly savings amount should thus take into account the loss due to inflation and make an adjustment to compensate for that.

Goal : 100000.00
Months to reach the goal : 240
APR : 12%
Inflation : 8%

Should you like more info send mail to andreb@vkb.co.za
The final result will still be posted here for everyone to view and use.

OK its clear now - i'll answer you in short time. (1-2 of Feb)

@SpideyMod
????
Where is your pre-PAQ post?

I must admit that I have forgotten this question. However you should make pre-PAQ post with some time allowing participants to react, am I right?

I'm still awaiting word from zlatev who is working on the question......

I'm still awaiting word from zlatev who is working on the question......

'Beneke - check this out:

Function fv(pv, yrs, intr)
   fv = pv * ((1 + intr) ^ yrs)
End Function

Function pv(fv, yrs, intr)
    pv = fv / ((1 + intr) ^ yrs)
End Function

Sub calc()
goalamount = 1000000
inflationrate = 8 / 100
growthrate = 14 / 100
presentsavings = 0
yearstogoal = 20

' Calculated on yearly basis
future_presentsavings = fv(presentsavings, yearstogoal, growthrate)
future_goal = fv(goalamount, yearstogoal, inflationrate)
future_additionalsavings = future_goal - future_presentsavings

'Calculating anuitet (monthly savings) index
months = yearstogoal * 12
monthlygrowthrate = growthrate / 12
monthlysavingsindex = (((1 + monthlygrowthrate) ^ months) - 1) / monthlygrowthrate

'Calculating monthly payment
monthlypayment = future_additionalsavings / monthlysavingsindex

MsgBox "Monthly Payment = " & Format(monthlypayment, "$#.00") & Chr(13) & _
      "Principal = " & Format(future_goal, "$#.00") & Chr(13) & _
      "Decay = " & Format(future_goal - goalamount, "$#.00") & Chr(13) & _
      "Total = " & Format(goalamount, "$#.00")

End Sub

zlatev,

The pre-PAQ post came from DanRollins on  01/26/2003 06:51PM PST.

I looked over the responses from past that point and the latest one was from you stating you would return on 1-2 Feb.  You did not return.  1 week past that point and still no response.  So, I PAQ'd it. This is not an unfixable situation, but it really is your responsibility to stay on top of the questions you are working especially after a resolution notice is sent out.  I hope you come to a resolution on this.  If you do, drop a line here and points will be assigned.  As it stands, people looking for a resolution to this problem can find value in the question's answers so it is PAQd.

@beneke, hara is what mods say about this:
https://www.experts-exchange.com/questions/20507446/Q-20302926-PAQ'd-by-mistake.html#1

@beneke, hara is what mods say about this:
https://www.experts-exchange.com/questions/20507446/Q-20302926-PAQ'd-by-mistake.html#1

@beneke, Did the sub I have provided to you calculate properly?

Please let me know, so that to improve it to match your requirements:

"The result should tell me in a table:
a) what the user should save each month to reach the goal
b) the interest earned for the month
c) the total interest earned
d) loss due to inflation for the month
e) total inflation"

Kind Regards,
Zlatin Zlatev

Hi zlatev,

Plse check this out. It's rough and not commented very well. This is what I've come up with in the mean time. I've also done the table should you wish to see it.

I'm still looking at yours but I guess I will need the table to to see the figures. In the mean time, thanks.

"-------------------------------------------

Public Function CalculateGoal(Amount As Double, Saved As Double, _
  APR As Double, Length As Double, Inflation As Double, _
  txtMonthPayment As TextBox, txtTotInterest As TextBox, txtTotPrincipal As TextBox, _
  txtTotInflation As TextBox, txtTotInfMonthly As TextBox, _
  txtTotInfInterest As TextBox, txtTotInfPrincipal As TextBox, _
  Optional sMessage As String = "")
  ' ***************************************************************************
  ' Procedure    : CalculateGoal in file clsBSCalculations
  '
  ' Purpose      : Calculate a savings goal monthly payment
  '
  ' Parameters   : Amount = Goal Amount to reach
  '                Saved = Amount already saved
  '                APR = APR as received from the financial institution
  '                Length = Months to reach the goal
  '                Inflation = Decay in capital
  '
  ' Return Values: txtMonthPayment = Monthly payment amount
  '                txtTotInterest = Total interest received
  '                txtTotPrincipal = Total principal received
  '
  '                txtTotInflation = Calculate decay
  '
  '                txtTotInfMonthly = Monthly payment amount(D)
  '                txtTotInfInterest = Total interest received(D)
  '                txtTotInfPrincipal = Total principal received(D)
  '
  ' Special Logic: None
  '
  ' ===========================================================================
  ' Author    : Andre Beneke
  ' DateTime  : 22/06/2002 21:00
  ' ***************************************************************************
  Dim dAPR           As Double
  Dim dINF           As Double

  Dim dPrincipal     As Double
  Dim dTotPrinc      As Double
  Dim dInterest      As Double
  Dim dTotInt        As Double
  Dim dInflation     As Double
  Dim dTotInf        As Double

  Dim dPV            As Double
  Dim dFV            As Double
  Dim dPMT           As Double

  Dim iPeriod        As Integer

  On Error GoTo CalculateGoal_Error

  ' Convert all % to same format
  dAPR = (APR / 1200)
  dINF = (Inflation / 1200)

  ' Calculate before inflation
  dPMT = -Pmt(dAPR, Length, -Saved, Amount, 0)
  dPV = -PV(dAPR, Length, -dPMT, Amount, 0)
  dFV = FV(dAPR, Length, -dPMT, -dPV, 0)

  For iPeriod = 1 To Length
    dInterest = (IPmt(dAPR, iPeriod, Length, -dPV, dFV, 0))
    dTotInt = (dTotInt + dInterest)

    dPrincipal = dPMT
    dTotPrinc = (dTotPrinc + dPrincipal)

    dInflation = ((dTotPrinc + Saved) * dINF)
    dTotInf = (dTotInf + dInflation)
  Next iPeriod

  dTotPrinc = dTotPrinc + Saved
 
  txtMonthPayment.Text = Format(dPMT, "#0.00")
  txtTotInterest.Text = Format(dTotInt, "#0.00")
  txtTotPrincipal.Text = Format(dTotPrinc, "#0.00")
  txtTotInflation.Text = Format(dTotInf, "#0.00")
 
  ' Calculate inflation
  dPMT = -Pmt(dAPR, Length, -Saved, (Amount + dTotInf), 0)
  dPV = -PV(dAPR, Length, -dPMT, (Amount + dTotInf), 0)
  dFV = FV(dAPR, Length, -dPMT, -dPV, 0)

  dTotInt = 0
  dTotPrinc = 0

  For iPeriod = 1 To Length
    dInterest = (IPmt(dAPR, iPeriod, Length, -dPV, dFV, 0))
    dTotInt = (dTotInt + dInterest)

    dPrincipal = dPMT
    dTotPrinc = (dTotPrinc + dPrincipal)
  Next iPeriod

  dTotPrinc = dTotPrinc + Saved

  ' Inflation Monthly payment
  txtTotInfMonthly.Text = Format(dPMT, "#0.00")
  txtTotInfInterest.Text = Format(dTotInt, "#0.00")
  txtTotInfPrincipal.Text = Format(dTotPrinc, "#0.00")

  Exit Function

CalculateGoal_Error:

  sMessage = "Error calculating goal values"
End Function

Hi zlatev,

Plse check this out. It's rough and not commented very well. This is what I've come up with in the mean time. I've also done the table should you wish to see it.

I'm still looking at yours but I guess I will need the table to to see the figures. In the mean time, thanks.

"-------------------------------------------

Public Function CalculateGoal(Amount As Double, Saved As Double, _
  APR As Double, Length As Double, Inflation As Double, _
  txtMonthPayment As TextBox, txtTotInterest As TextBox, txtTotPrincipal As TextBox, _
  txtTotInflation As TextBox, txtTotInfMonthly As TextBox, _
  txtTotInfInterest As TextBox, txtTotInfPrincipal As TextBox, _
  Optional sMessage As String = "")
  ' ***************************************************************************
  ' Procedure    : CalculateGoal in file clsBSCalculations
  '
  ' Purpose      : Calculate a savings goal monthly payment
  '
  ' Parameters   : Amount = Goal Amount to reach
  '                Saved = Amount already saved
  '                APR = APR as received from the financial institution
  '                Length = Months to reach the goal
  '                Inflation = Decay in capital
  '
  ' Return Values: txtMonthPayment = Monthly payment amount
  '                txtTotInterest = Total interest received
  '                txtTotPrincipal = Total principal received
  '
  '                txtTotInflation = Calculate decay
  '
  '                txtTotInfMonthly = Monthly payment amount(D)
  '                txtTotInfInterest = Total interest received(D)
  '                txtTotInfPrincipal = Total principal received(D)
  '
  ' Special Logic: None
  '
  ' ===========================================================================
  ' Author    : Andre Beneke
  ' DateTime  : 22/06/2002 21:00
  ' ***************************************************************************
  Dim dAPR           As Double
  Dim dINF           As Double

  Dim dPrincipal     As Double
  Dim dTotPrinc      As Double
  Dim dInterest      As Double
  Dim dTotInt        As Double
  Dim dInflation     As Double
  Dim dTotInf        As Double

  Dim dPV            As Double
  Dim dFV            As Double
  Dim dPMT           As Double

  Dim iPeriod        As Integer

  On Error GoTo CalculateGoal_Error

  ' Convert all % to same format
  dAPR = (APR / 1200)
  dINF = (Inflation / 1200)

  ' Calculate before inflation
  dPMT = -Pmt(dAPR, Length, -Saved, Amount, 0)
  dPV = -PV(dAPR, Length, -dPMT, Amount, 0)
  dFV = FV(dAPR, Length, -dPMT, -dPV, 0)

  For iPeriod = 1 To Length
    dInterest = (IPmt(dAPR, iPeriod, Length, -dPV, dFV, 0))
    dTotInt = (dTotInt + dInterest)

    dPrincipal = dPMT
    dTotPrinc = (dTotPrinc + dPrincipal)

    dInflation = ((dTotPrinc + Saved) * dINF)
    dTotInf = (dTotInf + dInflation)
  Next iPeriod

  dTotPrinc = dTotPrinc + Saved
 
  txtMonthPayment.Text = Format(dPMT, "#0.00")
  txtTotInterest.Text = Format(dTotInt, "#0.00")
  txtTotPrincipal.Text = Format(dTotPrinc, "#0.00")
  txtTotInflation.Text = Format(dTotInf, "#0.00")
 
  ' Calculate inflation
  dPMT = -Pmt(dAPR, Length, -Saved, (Amount + dTotInf), 0)
  dPV = -PV(dAPR, Length, -dPMT, (Amount + dTotInf), 0)
  dFV = FV(dAPR, Length, -dPMT, -dPV, 0)

  dTotInt = 0
  dTotPrinc = 0

  For iPeriod = 1 To Length
    dInterest = (IPmt(dAPR, iPeriod, Length, -dPV, dFV, 0))
    dTotInt = (dTotInt + dInterest)

    dPrincipal = dPMT
    dTotPrinc = (dTotPrinc + dPrincipal)
  Next iPeriod

  dTotPrinc = dTotPrinc + Saved

  ' Inflation Monthly payment
  txtTotInfMonthly.Text = Format(dPMT, "#0.00")
  txtTotInfInterest.Text = Format(dTotInt, "#0.00")
  txtTotInfPrincipal.Text = Format(dTotPrinc, "#0.00")

  Exit Function

CalculateGoal_Error:

  sMessage = "Error calculating goal values"
End Function

There are some functions in the script that are not standard VB functions. I mean:
PV (present value?)
FV (future value?)
Pmt (anuitet/monthly payment?)
IPmt (interest part from the payment?)

Is my guessings right?


Also do you use VBA in Excel and not pure VB6?

Regards,
Zlatin Zlatev

Or do you use VB.NET?

also read "anuitet" as "annuity" in my previous posts.

Hi zlatev,

I'm using VB6, SP5

If I just start a new project and type "pv(" I immediately have a box popup with the respective required parms. No special OCX or refferences

The refferences used by default are in this order, top to bottom:
Visual Basic for Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation

Regards
Beneke

OK... I will proceed in creating you a detailed calculation.


Just asking... do you know is there anything wrong with your code?

Or you have solved your problem and just want to see if there is cleaner (better documented) calculation procedure?

Kind Regards,
Zlatin Zlatev

I guess that I have solved the problem but would like you to have a go at it and see if you agree with the calculations.

Well, acording to me you do not count the inflation over the interest.

I mean this line:

dInflation = ((dTotPrinc + Saved) * dINF)

Correct me if I am wrong...
Actually I did not run your code (sorry, I do not have inet at home yet) and have made the program myself.

See below:

VERSION 5.00
Begin VB.Form Form1
   Caption         =   "Form1"
   ClientHeight    =   3195
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4785
   LinkTopic       =   "Form1"
   ScaleHeight     =   3195
   ScaleWidth      =   4785
   StartUpPosition =   3  'Windows Default
   Begin VB.ListBox List1
      BeginProperty Font
         Name            =   "Courier New"
         Size            =   8.25
         Charset         =   204
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   2790
      Left            =   0
      TabIndex        =   0
      Top             =   0
      Width           =   4575
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Sub calc(goalamount As Double, _
         inflationrate As Double, _
         growthrate As Double, _
         presentsavings As Double, _
         yearstogoal As Double)

Dim monthstogoal, month As Integer
Dim monthlygrowthrate As Double
Dim monthlyinflationrate As Double
Dim fvgoalamount As Double
Dim monthlypayment As Double

Dim monthlyinterest, totalinterest As Double
Dim monthlydecay, totaldecay As Double
Dim nominalprincipal As Double

Dim StrFmt As String
StrFmt = "$###,###,##0.00"

' Monthly calculations - mothly rates are calculated via
' bank monthly rate method: montlyrate = annualrate * (1/12)
'
' The opposite method is mathematical rate method which will calculate:
' monthlyrate = ((1+annualrate)^(1/12))-1
' There is significant difference between two methods so you should know how
' your bank calculates your monthly rate (typically the first method is used)
'
' in all calculations "compound interest" is assumed method for accumulating
'

monthstogoal = yearstogoal * 12
monthlygrowthrate = growthrate * (1 / 12)
monthlyinflationrate = inflationrate * (1 / 12)

' We want to achieve in the future present value equal to goalamount
' starting from presentsavings:

fvgoalamount = FV(monthlyinflationrate, monthstogoal, presentsavings, -goalamount)
monthlypayment = Pmt(monthlygrowthrate, monthstogoal, presentsavings, -fvgoalamount)


List1.AddItem ("Monthly Payment = " & Format(monthlypayment, StrFmt))
List1.AddItem (String(7 * 16, "_"))
List1.AddItem ( _
                 Right(Space(15) & "Month.Interest", 15) & Chr(9) & _
                 Right(Space(15) & "Total Interest", 15) & Chr(9) & _
                 Right(Space(15) & "NominalPrincip.", 15) & Chr(9) & _
                 Right(Space(15) & "NominalBalance", 15) & Chr(9) & _
                 Right(Space(15) & "MonthlyDecay", 15) & Chr(9) & _
                 Right(Space(15) & "Total Decay", 15) & Chr(9) & _
                 Right(Space(15) & "Real balance", 15) & Chr(9) & _
                 "")
List1.AddItem (String(7 * 16, "_"))

totalinterest = 0
totaldecay = 0
nominalprincipal = presentsavings

For month = 1 To monthstogoal
  monthlyinterest = IPmt(monthlygrowthrate, month, monthstogoal, -presentsavings, fvgoalamount)
  totalinterest = totalinterest + monthlyinterest
  nominalprincipal = nominalprincipal + monthlypayment
'
' Decay is calculated using "real vs. nominal rate equilibrium":
' (1+nominal_rate) = (1+real_rate)*(1+inflation)
'
' thus capital decay is calculated as discount from the current balance
' using inflation as discount rate (monthly decay is substraction between two consecutive total decays):
'
  monthlydecay = (totalinterest + nominalprincipal) - _
                 (totalinterest + nominalprincipal) / ((1 + monthlyinflationrate) ^ month) - _
                 totaldecay
  totaldecay = totaldecay + monthlydecay
 
 
  List1.AddItem ( _
                 Right(Space(15) & Format(monthlyinterest, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(totalinterest, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(nominalprincipal, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(nominalprincipal + totalinterest, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(monthlydecay, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(totaldecay, StrFmt), 15) & Chr(9) & _
                 Right(Space(15) & Format(nominalprincipal + totalinterest - totaldecay, StrFmt), 15) & Chr(9) & _
                 "")
Next

End Sub

Private Sub Form_Initialize()
calc goalamount:=100000, inflationrate:=8 / 100, growthrate:=14 / 100, presentsavings:=0, yearstogoal:=20
End Sub

Private Sub Form_Resize()
List1.Height = Me.Height - 500
List1.Width = Me.Width - 150
End Sub

copy the above code and save it as form1.frm
You will see "Nominal Balance" and "Real Balance" columns
that will show how much dollars do you have in your account, and what is their value in today's dollars.
Final row of Real ballance should be equal to your GOAL amount

Also note that monthly decay is calculated as decay DURING the month and the monthly payment (annuity) and interest are payment and interest AT THE BEGINNING of the month.

Hi zlatev,
This rocks, any clue on how I can accept your comment as an answer.

BTW. Can I call on you again if do get stuck?

Yep, surelly you can call me, if you need more help with that kind of problems... (actually my university degree is BA in Finance, so it is pleasure for me to do it) :)

As for the answer - we will need moderator's help here... I will contact them in Community Support area for this.

As I was on this thread previously, there's no need to drop  a CS note, if you already have, I will find it.  Here's what will happen since the points have already been refunded and this question has been PAQ'd.

beneke,
You can create a "points for zlatev" question in this topic area. You can click on the link for easy access: https://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/

Have it say something like "points for zlatev re 20302926"

And in the comment you can show the link to this question: https://www.experts-exchange.com/questions/20302926/Help-on-Financial-formula.html so that other experts know where the original question was.  Then assign the points that this question was worth (or whatever you wanted to give to zlatev).  When that is done, you can come back here and leave a note for zlatev to look for the new question (it is sometimes helpful to provide the link, but I suspect zlatev has the skills to find it if you cannot).

zlatev, if beneke forgets to reference this question, please add it to your comments.  If for some reason the "points for" question doesn't get created, drop another CS question in a couple of days and I will create one for you.

If there are further questions on this process, feel free to drop a note here and I will assist.

Thanks to both of you for bringing this to a successful conclusion!

SpideyMod
Community Support Moderator @Experts Exchange

points awarded at:
https://www.experts-exchange.com/questions/20532442/Question-for-Zlatev.html 

Thanks again beneke