Solved

MS Excel - Formula to find loan payoff date

Posted on 2009-04-20
Medium Priority
3,222 Views
Is there a one line forumla to calculate the payoff date in Microsoft Excel. We have been using the following calculator as a work around http://www.alstonlabs.com/loancalc.htm.  Please see attached spreadsheet. Woud like to know if there is a way to find the payoff date w/o having to list each payment date.
Thank you!
Formula.xls
0
Question by:ASHPHelpDesk
• 2
• 2

LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 24184038
You can use the following formula to find the number of payments
=NPER(0,250/3,-5000)
where 0 is the interest rate for each period, 250/3 [=\$83.33] the payment for each period and -5000 is the amount of the loan (shown as a negative number).
For this example you'll get an answer of 60. To convert that to an end date then, assuming start date in A1and that each period is a month
=DATE(YEAR(A1),MONTH(A1)+NPER(0,250/3,-5000),DAY(A1))
regards, barry
0

LVL 50

Expert Comment

ID: 24184119
...or perhaps I misunderstood.....?
If you already have the term (5 years), can't you just add that to the start date to get the payoff date? For example, with your example
=DATE(YEAR(A6)+D2,MONTH(A6),DAY(A6))
or am  missing something?
barry

0

LVL 93

Expert Comment

ID: 24184204
Hello ASHPHelpDesk,

I agree with Barry, in that as long as there are no extra payments to principal, then if you know the terms
of the loan you know exactly what the payoff date is--5 years in this case, and you can determine the
payoff date rather easily.

This only becomes tricky if the borrower is making extra payments against principal.  The code below
uses one UDF to return an array with the loan amortization schedule, and a second UDF to determine
the date of the last payment.  Assuming you put the extra payment to principal in, say, F2 (next to your
other parameters for the loan), the formula becomes:

=PayoffDate(A6,3,B2,C2/12,D2*12,F2)

Regards,

Patrick
``````' Function by Patrick Matthews
Option Explicit
Enum PaymentIntervals
pmtDaily = 0
pmtWeekly = 1
pmtBiWeekly = 2
pmtMonthly = 3
pmtQuarterly = 4
pmtSemiAnnually = 5
pmtAnnually = 6
End Enum
Function PayoffDate(FirstPayDate As Date, Interval As PaymentIntervals, BeginPrincipal As Double, _
PeriodRate As Double, Periods As Long, Optional ExtraPrin As Double = 0)

Dim SchedArr As Variant
Dim Counter As Long

SchedArr = AmortSchedTraditional(BeginPrincipal, PeriodRate, Periods, ExtraPrin)
For Counter = 1 To UBound(SchedArr, 1)
If SchedArr(Counter, 5) = 0 Then Exit For
Next

Select Case Interval
Case pmtDaily: PayoffDate = DateAdd("d", Counter - 1, FirstPayDate)
Case pmtWeekly: PayoffDate = DateAdd("ww", Counter - 1, FirstPayDate)
Case pmtBiWeekly: PayoffDate = DateAdd("ww", 2 * (Counter - 1), FirstPayDate)
Case pmtMonthly: PayoffDate = DateAdd("m", Counter - 1, FirstPayDate)
Case pmtQuarterly: PayoffDate = DateAdd("q", Counter - 1, FirstPayDate)
Case pmtSemiAnnually: PayoffDate = DateAdd("m", 6 * (Counter - 1), FirstPayDate)
Case pmtAnnually: PayoffDate = DateAdd("yyyy", Counter - 1, FirstPayDate)
End Select

End Function
Function AmortSchedTraditional(ByRef BeginPrincipal As Double, ByRef PeriodRate As Double, ByRef Periods As Long, _
Optional ByRef ExtraPrin As Double = 0)
' This function returns an array with the amortization schedule for a loan with known
' beginning principal, period rate, number of periods, and optional fixed extra principal
' with each payment.  All arguments should be positive

' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments
' ultimately needed to retire the loan (may be different from initial Periods argument if extra

' In the second dimension, the values are:
' 1: Balance before payment X
' 2: Total amount of payment X
' 3: Principal amount of payment X
' 4: Interest amount of payment X
' 5: Balance after payment X

' To use this function in Excel, use an array formula.  If the array area has more rows than the
' function returns, the 'extra' rows will show a #N/A error.  You can use Conditional Formatting
' to dynamically hide such results.

' This function will work in other VBA and VB projects.

Dim Schedule() As Double
Dim Schedule2() As Double
Dim BeginBal As Double
Dim Counter As Long
Dim Counter2 As Long
Dim LevelPay As Double

' Dimension array with 'first pass' amortization schedule
ReDim Schedule(1 To Periods, 1 To 5) As Double

' Negative extra payments are suppressed
If ExtraPrin < 0 Then ExtraPrin = 0

BeginBal = BeginPrincipal
LevelPay = -Pmt(PeriodRate, Periods, BeginPrincipal) + ExtraPrin

For Counter = 1 To Periods
Schedule(Counter, 1) = BeginBal
' Interest is easy to determine; do it first
Schedule(Counter, 4) = BeginBal * PeriodRate
' Amount of principal portion depends in part on the remaining balance before the payment
' is applied; the principal amount can never be larger than this
Schedule(Counter, 3) = IIf((LevelPay - Schedule(Counter, 4)) < BeginBal, _
LevelPay - Schedule(Counter, 4), BeginBal)
' Total payment = principal portion + interest portion
Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4)
' Determine balance after payment is applied.  Check for a balance approaching zero
Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3)
If Schedule(Counter, 5) < 0.01 Then
Schedule(Counter, 5) = 0
Exit For
End If
BeginBal = Schedule(Counter, 5)
Next

' Create a new array that has only as many 'rows' as there are payments to be made
ReDim Schedule2(1 To Counter, 1 To 5) As Double
For Counter2 = 1 To Counter
Schedule2(Counter2, 1) = Schedule(Counter2, 1)
Schedule2(Counter2, 2) = Schedule(Counter2, 2)
Schedule2(Counter2, 3) = Schedule(Counter2, 3)
Schedule2(Counter2, 4) = Schedule(Counter2, 4)
Schedule2(Counter2, 5) = Schedule(Counter2, 5)
Next

' Assign the function return value

End Function
``````
0

LVL 93

Expert Comment

ID: 24184289
I just remembered that mortgages in some countries (such as Canada) are typically set up with semi-monthly
payments, so I altered my code above to allow for that payment interval.  The formula I provided did not change.
``````' Function by Patrick Matthews
Option Explicit
Enum PaymentIntervals
pmtDaily = 0
pmtWeekly = 1
pmtBiWeekly = 2
pmtMonthly = 3
pmtSemiMonthly = 4
pmtQuarterly = 5
pmtSemiAnnually = 6
pmtAnnually = 7
End Enum
Function PayoffDate(FirstPayDate As Date, Interval As PaymentIntervals, BeginPrincipal As Double, _
PeriodRate As Double, Periods As Long, Optional ExtraPrin As Double = 0)

Dim SchedArr As Variant
Dim Counter As Long
Dim Date1 As Date
Dim Date2 As Date

SchedArr = AmortSchedTraditional(BeginPrincipal, PeriodRate, Periods, ExtraPrin)
For Counter = 1 To UBound(SchedArr, 1)
If SchedArr(Counter, 5) = 0 Then Exit For
Next

Select Case Interval
Case pmtDaily: PayoffDate = DateAdd("d", Counter - 1, FirstPayDate)
Case pmtWeekly: PayoffDate = DateAdd("ww", Counter - 1, FirstPayDate)
Case pmtBiWeekly: PayoffDate = DateAdd("ww", 2 * (Counter - 1), FirstPayDate)
Case pmtMonthly: PayoffDate = DateAdd("m", Counter - 1, FirstPayDate)
Case pmtSemiMonthly
If Counter Mod 2 = 0 Then 'even number of payments
PayoffDate = DateAdd("m", (Counter - 1) / 2, FirstPayDate)
Else
Date1 = DateAdd("m", Int((Counter - 1) / 2), FirstPayDate)
PayoffDate = DateAdd("d", Int(DateDiff("d", Date1, Date2) / 2), Date1)
End If
Case pmtQuarterly: PayoffDate = DateAdd("q", Counter - 1, FirstPayDate)
Case pmtSemiAnnually: PayoffDate = DateAdd("m", 6 * (Counter - 1), FirstPayDate)
Case pmtAnnually: PayoffDate = DateAdd("yyyy", Counter - 1, FirstPayDate)
End Select

End Function
Function AmortSchedTraditional(ByRef BeginPrincipal As Double, ByRef PeriodRate As Double, ByRef Periods As Long, _
Optional ByRef ExtraPrin As Double = 0)
' This function returns an array with the amortization schedule for a loan with known
' beginning principal, period rate, number of periods, and optional fixed extra principal
' with each payment.  All arguments should be positive

' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments
' ultimately needed to retire the loan (may be different from initial Periods argument if extra

' In the second dimension, the values are:
' 1: Balance before payment X
' 2: Total amount of payment X
' 3: Principal amount of payment X
' 4: Interest amount of payment X
' 5: Balance after payment X

' To use this function in Excel, use an array formula.  If the array area has more rows than the
' function returns, the 'extra' rows will show a #N/A error.  You can use Conditional Formatting
' to dynamically hide such results.

' This function will work in other VBA and VB projects.

Dim Schedule() As Double
Dim Schedule2() As Double
Dim BeginBal As Double
Dim Counter As Long
Dim Counter2 As Long
Dim LevelPay As Double

' Dimension array with 'first pass' amortization schedule
ReDim Schedule(1 To Periods, 1 To 5) As Double

' Negative extra payments are suppressed
If ExtraPrin < 0 Then ExtraPrin = 0

BeginBal = BeginPrincipal
LevelPay = -Pmt(PeriodRate, Periods, BeginPrincipal) + ExtraPrin

For Counter = 1 To Periods
Schedule(Counter, 1) = BeginBal
' Interest is easy to determine; do it first
Schedule(Counter, 4) = BeginBal * PeriodRate
' Amount of principal portion depends in part on the remaining balance before the payment
' is applied; the principal amount can never be larger than this
Schedule(Counter, 3) = IIf((LevelPay - Schedule(Counter, 4)) < BeginBal, _
LevelPay - Schedule(Counter, 4), BeginBal)
' Total payment = principal portion + interest portion
Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4)
' Determine balance after payment is applied.  Check for a balance approaching zero
Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3)
If Schedule(Counter, 5) < 0.01 Then
Schedule(Counter, 5) = 0
Exit For
End If
BeginBal = Schedule(Counter, 5)
Next

' Create a new array that has only as many 'rows' as there are payments to be made
ReDim Schedule2(1 To Counter, 1 To 5) As Double
For Counter2 = 1 To Counter
Schedule2(Counter2, 1) = Schedule(Counter2, 1)
Schedule2(Counter2, 2) = Schedule(Counter2, 2)
Schedule2(Counter2, 3) = Schedule(Counter2, 3)
Schedule2(Counter2, 4) = Schedule(Counter2, 4)
Schedule2(Counter2, 5) = Schedule(Counter2, 5)
Next

' Assign the function return value

End Function
``````
0

Author Closing Comment

ID: 31572190
Thank you!
0

Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Gain an elementary understanding of Blockchain technology.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month13 days, 8 hours left to enroll