Solved

Help on Financial formula

Posted on 2002-05-20
40
455 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:beneke
Comment Utility
To Hornet241

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

BTW, thanks
0
 

Author Comment

by:beneke
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Send it to hornet241@hotmail.com
0
 

Author Comment

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

Expert Comment

by:zlatev
Comment Utility
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
Comment Utility
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
Comment Utility
Hi zlatev, still wan't to help out here.
0
 
LVL 8

Expert Comment

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

Author Comment

by:beneke
Comment Utility
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
Comment Utility
OK its clear now - i'll answer you in short time. (1-2 of Feb)
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
Comment Utility
PAQ'd and refunded

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 8

Expert Comment

by:zlatev
Comment Utility
@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
Comment Utility
I'm still awaiting word from zlatev who is working on the question......
0
 

Author Comment

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

Expert Comment

by:zlatev
Comment Utility
'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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Expert Comment

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

Expert Comment

by:zlatev
Comment Utility
0
 
LVL 8

Expert Comment

by:zlatev
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Or do you use VB.NET?
0
 
LVL 8

Expert Comment

by:zlatev
Comment Utility
also read "anuitet" as "annuity" in my previous posts.
0
 

Author Comment

by:beneke
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

17 Experts available now in Live!

Get 1:1 Help Now