Multiple functions or Just one ? (1 Viewer)

[WineSnob]

I am trying to calculate monthly interest on a value after each withdrawl.
The starting amount is 175502.95
The monthly withdrawl is 5000
The annual interest rate is 1% or .0008333 (.01/12)
start 175502.95 5000 0.01 0.000833333

1 175502.95 5000 170502.95 142.0857917
2 170645.0358 5000 165645.0358 138.0375298
3 165783.0733 5000 160783.0733 133.9858944
4 160917.0592 5000 155917.0592 129.9308827
5 156046.9901 5000 151046.9901 125.8724917
6 151172.8626 5000 146172.8626 121.8107188
7 146294.6733 5000 141294.6733 117.7455611
8 141412.4189 5000 136412.4189 113.6770157
9 136526.0959 5000 131526.0959 109.6050799
10 131635.701 5000 126635.701 105.5297508
11 126741.2307 5000 121741.2307 101.4510256
12 121842.6817 5000 116842.6817 97.36890145
sum of interest 1437.100644
Here is the function I have so far. It does calcluate month 1 and 2 interest.
Is it possible to get the results to be $1437.10 in ONE function?
'****Get monthly Interest for Segment1
Public Function fnSeg1Int(nMonth As Integer, SegPV As Currency, WDAmt As Currency, nRate As Double) As Currency
Dim I As Integer
Dim NextStarting As Currency
For I = 1 To nMonth
If I = 1 Then
Interest = (SegPV - WDAmt) * (nRate / 12)

Else
NextStarting = (SegPV - WDAmt) + (SegPV - WDAmt) * (nRate / 12)
Interest = (NextStarting - WDAmt) * (nRate / 12)

End If

fnSeg1Int = Interest

Next I


End Function
Function testinterest()
Debug.Print fnSeg1Int(2, 175502.95, 5000, 0.01)
End Function

 

[Kiwiman]

Howzit

You may want to look at Khawar's loan repayment database to see if this helps

http://www.access-programmers.co.uk/forums/showthread.php?t=152420

 

[Kiwiman]

Howzit

Public Function fnSeg1Int(nMonth As Integer, SegPV As Currency, WDAmt As Currency, nRate As Double) As currency


Dim i As Integer
Dim nextStarting As Currency
Dim interest As Currency           ' holds the total interest
Dim perint As Currency              ' holds the current period interest

interest = 0                                ' set interest to 0
nextStarting = SegPV - WDAmt   ' calulate the starting value


For i = 1 To nMonth
    perint = nextStarting * nRate / 12       ' calculate the current period interest
    interest = interest + perint                  ' add to interest variable to accumulate
    nextStarting = nextStarting - WDAmt + perint  ' recalculate the nextstating by adding back interest and taking away withdrawal
Next i

fnSeg1Int= interest

End Function

 

[WineSnob]

Thanks Kiwiman PERFECT.