Loan reamortization

  • Thread starter Thread starter skoalnreds
  • Start date Start date
S

skoalnreds

Hi,

I need to recalculate repayment amounts for 401(k) loans due to
changes in employee pay frequency (weekly to monthly, monthly to
biweekly, biweekly to semimonthly, etc.). Has anyone ever had to do
this?

The information I have is:
- Original Loan Amount
- Annual Interest Rate
- Original Total # of Payments
- "Old" employee pay (re-pay) frequency
- Original repayment amount
- Payoff Date (Note: I've been told that this date must not change
per IRS rules.)
- Current Balance Remaining
- "New" employee pay (re-pay) frequency

Thank you for your time and for any help you can provide.
 
skoalnreds said:
I need to recalculate repayment amounts for 401(k) loans due to
changes in employee pay frequency (weekly to monthly, monthly to
biweekly, biweekly to semimonthly, etc.).
Click to expand...

The loan payment can be computed by:

=ROUNDUP(PMT(annualRate/frequency, nPayPeriods, -remainingBalance), 2)

where "frequency" is 52, 26, 24 and 12 for weekly, biweekly, semimonthly and
monthly respectively.

The key is "nPayPeriods". That depends on a piece information you did not
mention, namely: the date of the first payroll deduction on the new pay
frequency.

That date is presumably chosen so that the last payroll deduction on the new
pay frequency corresponds to the original payoff date.

Moreover, "remainingBalance" is the remaining balance as of that date. So
it might be less than the remaining balance today.

If you do not have that date and the corresponding remaining balance, you
will have to estimate them. Do you need help in estimating them?

Assuming you have the correct date, "nPayPeriods" can be computed as
follows:

weekly: =1 + DATEDIF(firstDate,lastDate,"d")/7

biweekly: =1 + DATEDIF(firstDate,lastDate,"d")/14

semimonthly: =1 + DATEDIF(firstDate,lastDate,"m")*2 + oddSemimonth

monthly: =1 + DATEDIF(firstDate,lastDate,"m")

After entering these formulas, the cells must be (re)formatted explicitly as
General.

Note that the semimonthly DATEDIF expression needs an adjustment if
"firstDate" is not an integral number of months before "lastDate". Then
"oddSemimonth" is TRUE; otherwise, it is FALSE. "oddSemimonth" can be
determined manually, or it can be computed by:

=(EDATE(lastDate,-DATEDIF(firstDate,lastDate,"m")) <> firstDate)

If you get a #NAME?, see the EDATE help page for the remedy. Of course,
this formula could be incorporated into the semimonthly formula above.

Much of this can covered by a VLOOKUP table that depends on the new pay
frequency, if need that robustness. Do you need help with that design?

Does that answer your question?


----- original message -----
 
Yes, I also have the date of the first repayment under the new
frequency schedule. Thank you so much. I can definitely handle it
from here. It was just a matter of not knowing the formula. Thanks
again.
 
Back
Top