How can I can calculate loan repayment with some skips payments

[hbsqn]

Dear All,


I know that PMT formula can calculate loan repayment. but I believe it will do so with equal payments basis. What if I want to calculate loan repayments which to be paid back by 32 monthly equal installments over 36 months term.


I am assuming that I will skip paying months: 5, 13, 27, 32


Is there a way to solve such computation issue please?

 

[Maverick27]

PMT formula is the correct function to use.



Use the PMT Formula shown in the screenshot & Change Year Parameter to 3, Payment Per Year to:32 & all other Variables too.

 

[joeu2004]

I know that PMT formula can calculate loan repayment. but I believe it will do so with equal payments basis. What if I want to calculate loan repayments which to be paid back by 32 monthly equal installments over 36 months term. I am assuming that I will skip paying months: 5, 13, 27, 32[.] Is there a way to solve such computation issue please?

I do not believe we can do it with an Excel formula. Instead, I would set up an amortization schedule and use Solver to derive the payment. Here is an example:

B4:  =PMT(B2/12,B3,-B1)
E3:  =$B$5
F3:  =G2*(D3-D2)*$B$2/12
G3:  =G2+F3-E3
H34: =G34
Copy E3:G3 down through E34:G34

Note that some rows are hidden for brevity (9:12 and 16:25). They are like the rows above (8 and 15).

"PMT x36" in B4 is provided to demonstrate the difference. "PMT x32" in B5 is the amount of the equal 32 payments with skipped months, derived by Solver.

H34 shows the true value of G34.

Solver set-up:

B5:              empty
Objective Cell:  G34
To Value Of:     0
Variable Cell:   B5
Method:          GRG Nonlinear
Unconstrained Variables Nonnegative (check)
Options, GRG Nonlinear:
  Convergence: 0.00000001 (something smaller than default)

Caveat: "PMT x32" in B5 is not rounded. In the real-world, it will be.

 

[joeu2004]

Clarification....

"PMT x36" in B4 is provided to demonstrate the difference. "PMT x32" in B5 is the amount of the equal 32 payments with skipped months, derived by Solver.

Perhaps the following would be more instructive:

=PMT(B2/12,32,-B1)

is about 3,667.09, instead of about 3,732.68, derived by Solver.

 

[BadgerRoad]

It isn't possible to solve this using the PMT function as the result will be different depending upon which payments are skipped. You will need to use an amortization schedule specifying which payments to skip, then use Solver or Goal Seek. Solver is actually overkill so I would suggest using Goal Seek.

 

[hbsqn]

Dear Joeu2004,

Thanks for your great demonstration. I tried Solver and it gave me the right calculation similar to my loan system. I just adjusted in your amortization schedule by calculating monthly interest even for skip months. difference will be something like 23$ only.

Question will be now that this can't be done through formulas, right? it must be done through Solver only?

 

[joeu2004]

I tried Solver and it gave me the right calculation similar to my loan system. I just adjusted in your amortization schedule by calculating monthly interest even for skip months. difference will be something like 23$ only.

I, too, calculated monthly interest for skipped months. I think the difference is: I calculated simple interest; you (or your loan system) might be calculating compound interest. Whether or not unpaid interest compounds depends on the terms of the loan contract.

Question will be now that this can't be done through formulas, right? it must be done through Solver only?

I answered that at the outset. I wrote: "I do not believe we can do it with an Excel formula".

Whether you choose to use Solver, Goal Seek or your own VBA function or macro is a personal choice.

 

[MickG]

Possible Option:-

[COLOR="Navy"]Sub[/COLOR] MG13Jul43
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] t [COLOR="Navy"]As[/COLOR] Double, Sum [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Double, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
'[COLOR="Green"][B]Loan Period = 36 months[/B][/COLOR]
'[COLOR="Green"][B]Periods 5,15,27 & 32 are missed payments, but incur interest[/B][/COLOR]
'[COLOR="Green"][B]annual Interest rate = 5%[/B][/COLOR]
'[COLOR="Green"][B]Principle = 1000 (Pounds Whatevers)[/B][/COLOR]
r = 0.05 / 12: n = 36
Sum = 1000
[COLOR="Navy"]Do[/COLOR] Until Sum > -0.05 And Sum < 0.05
Sum = 1000: t = t + 0.001
    [COLOR="Navy"]For[/COLOR] p = 1 To n
        [COLOR="Navy"]If[/COLOR] p = 5 Or p = 15 Or p = 27 Or p = 32 [COLOR="Navy"]Then[/COLOR]
            Sum = Sum * (1 + r)
        [COLOR="Navy"]Else[/COLOR]
            Sum = (Sum * (1 + r)) - t
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]Loop[/COLOR]
MsgBox "Sum Remaining at end of 36 mths:- " & Sum & Chr(10) & "32 Repayments of:- " & Format(t, "0.00")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick