Solved

Help on Financial formula

Posted on 2002-05-20
40
461 Views
Last Modified: 2012-05-04
I would like to calculate the principal and the interest on a GOAL amount. The P + I (taken inflation into account) should thus add up to exactly the GOAL amount.

Input values...
GOAL to be reached: 1000000.00
APR               : 14%
Length            : 240 monthly payments (20Years)
Capital decay(Inf): 8%

Result set...
Monthly payment : ?    (includes amount to compencate for the decay in capital)
Principal : ?
Interest : ?
Capital decay : ?


Take the code below between the '**' and paste it into notepad and save the file as frmCalc.frm Open it up in VB(6)

'***********************************
VERSION 5.00
Begin VB.Form Form1
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "Goal saving"
   ClientHeight    =   4125
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   6105
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   4125
   ScaleWidth      =   6105
   StartUpPosition =   2  'CenterScreen
   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()

End Sub
'***********************************
0
Comment
Question by:beneke
  • 18
  • 14
  • 4
  • +2
40 Comments
 
LVL 3

Expert Comment

by:Hornet241
ID: 7024326
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")
       
0
 

Author Comment

by:beneke
ID: 7026423
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.
0
 
LVL 3

Expert Comment

by:Hornet241
ID: 7029088
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:beneke
ID: 7041902
To Hornet241

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

BTW, thanks
0
 

Author Comment

by:beneke
ID: 7046992
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
0
 
LVL 3

Expert Comment

by:Hornet241
ID: 7049798
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???

0
 

Author Comment

by:beneke
ID: 7053382
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.
0
 
LVL 3

Expert Comment

by:Hornet241
ID: 7053963
Send it to hornet241@hotmail.com
0
 

Author Comment

by:beneke
ID: 7058723
To Hornet241, I've send the code.
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7235638
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)
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7818315
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
0
 

Author Comment

by:beneke
ID: 7819067
Hi zlatev, still wan't to help out here.
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7821848
beneke, rephrase your question - just tell what data you have, and what you want to achieve.
0
 

Author Comment

by:beneke
ID: 7822357
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.

0
 
LVL 8

Expert Comment

by:zlatev
ID: 7827849
OK its clear now - i'll answer you in short time. (1-2 of Feb)
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 7909824
PAQ'd and refunded

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7916009
@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?

0
 

Author Comment

by:beneke
ID: 7916753
I'm still awaiting word from zlatev who is working on the question......
0
 

Author Comment

by:beneke
ID: 7916760
I'm still awaiting word from zlatev who is working on the question......
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7917418
'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
0
 

Expert Comment

by:SpideyMod
ID: 7917967
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.
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7931936
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7931938
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7942267
@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
0
 

Author Comment

by:beneke
ID: 7947814
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
0
 

Author Comment

by:beneke
ID: 7947863
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
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7947937
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
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7947949
Or do you use VB.NET?
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7947956
also read "anuitet" as "annuity" in my previous posts.
0
 

Author Comment

by:beneke
ID: 7948735
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
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7949857
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
0
 

Author Comment

by:beneke
ID: 7964015
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.
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7964481
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:
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7964501
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
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7964516
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
0
 
LVL 8

Expert Comment

by:zlatev
ID: 7964695
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.
0
 

Author Comment

by:beneke
ID: 8006537
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?
0
 
LVL 8

Expert Comment

by:zlatev
ID: 8006688
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.
0
 

Expert Comment

by:SpideyMod
ID: 8008063
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: http://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: http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20302926.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

0
 
LVL 8

Expert Comment

by:zlatev
ID: 8040079
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

808 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