?
Solved

MS Excel - Formula to find loan payoff date

Posted on 2009-04-20
5
Medium Priority
?
3,222 Views
Last Modified: 2012-05-06
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
Comment
Question by:ASHPHelpDesk
  • 2
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
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

by:barry houdini
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

by:Patrick Matthews
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
    ' principal payments are made)
    
    ' 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
    AmortSchedTraditional = Schedule2
       
End Function

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
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)
                Date2 = DateAdd("m", 1, Date1)
                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
    ' principal payments are made)
    
    ' 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
    AmortSchedTraditional = Schedule2
       
End Function

Open in new window

0
 

Author Closing Comment

by:ASHPHelpDesk
ID: 31572190
Thank you!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

750 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