PDA

View Full Version : VBA function for mortgage calculations



phild393
10-14-2013, 06:02 AM
Hey guys, my first time in the forum, so hope I'm doing this right.

I need to write a function in VBA that takes inputs (Principal, Interest Rate, Mortgage Length) and spits out an array with the payment dates, beginning principal, principal paid, interest paid, and ending principal.

I think I could probably slog through this on my own (pretty new to VBA), but was wondering if anybody already had something similar.

Thanks for your help.

-Phil

Kenneth Hobs
10-14-2013, 06:58 AM
Welcome to the forum!

It might be easier to just use a template.
http://office.microsoft.com/en-us/templates/mortgage-amortization-schedule-TC001056620.aspx
http://office.microsoft.com/en-us/excel-help/using-the-loan-amortization-and-loan-analysis-templates-HA001034640.aspx
http://office.microsoft.com/en-us/templates/loan-calculator-with-extra-payments-TC006206283.aspx
http://office.microsoft.com/en-us/templates/loan-amortization-schedule-TC010073881.aspx

phild393
10-14-2013, 07:09 AM
Thanks Kenneth, that is pretty useful.

But I've been asked to actually write a formula in VBA for use. So I have to find a way to code this.

Paul_Hossler
10-14-2013, 08:18 AM
I'd look at Ken's links to see the formulas etc. that are used. Converting to VBA is pretty streight forward

But .... assuming that this is not homework, then you could do something like this in an array. Everything is hard coded (30 years, 5%, etc.) but it might give you a start




Option Explicit

Sub Mort()

'row = number
'col 1 = date
'col 2 = pmt amount
'col 3 = remaining
Dim iPeriod As Long
Dim A(1 To 360, 1 To 3)

'example: $100,000 @ 5% / yr, 12 equal payments/year
A(1, 1) = #7/1/2014#
A(1, 2) = Application.WorksheetFunction.Pmt(0.05 / 12, 12 * 30, 100000)
A(1, 3) = 100000 + A(1, 2)

For iPeriod = 2 To 360
A(iPeriod, 1) = CDate(DateSerial(Year(A(iPeriod - 1, 1)), Month(A(iPeriod - 1, 1)) + 1, Day(A(iPeriod - 1, 1))))


Next iPeriod

End Sub




Paul