Results 1 to 13 of 13
Like Tree1Thanks
  • 1 Post By Wilmer

Math Help - Actuarial Loan Settlement equation

  1. #1
    Newbie
    Joined
    Dec 2011
    Posts
    3

    Actuarial Loan Settlement equation

    Iím looking for some assistance in deciphering the settlement formula detailed in the link below so I can put this into practical use in a spread sheet. What I am looking to achieve is being able to calculate a loan settlement value every month throughout the duration of a loan agreement so the output can be plottedon a graph. Whilst reasonably competentin Excel, this level of mathematical formula is proving a challenge. Any help is appreciated.

    Best regards,


    Appleman

    The Consumer Credit (Early Settlement) Regulations 2004

    Calculation of the amount of rebate


    4.-(1) The amount of the rebate is the difference between the total amount of the repayments of credit that would fall due for payment after the settlement date if early settlement did not take place and the amount given by the following formula
    Last edited by Appleman; December 18th 2011 at 01:11 AM. Reason: typo
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Moo
    Moo is offline
    A Cute Angle Moo's Avatar
    Joined
    Mar 2008
    From
    P(I'm here)=1/3, P(I'm there)=t+1/3
    Posts
    5,618
    Thanks
    6

    Re: Actuarial Loan Settlement equation

    Hello,

    And what are the data you already have ? I mean what are the constants you know in the formula ?
    Follow Math Help Forum on Facebook and Google+

  3. #3
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,085
    Thanks
    67

    Re: Actuarial Loan Settlement equation

    If regulations need to be read, suggest you get a lawyer!
    If I understand what you're trying to do, this should be quite sufficient:

    Code:
        Interest calculated        Interest precalculated   Refund if precalculated 
    0                  1000.00   |              1316.92   |         316.92
    1 -329.23  120.00   790.77   |    -329.23    987.69   |         196.92
    2 -329.23   94.89   556.43   |    -329.23    658.46   |         102.03 ***
    3 -329.23   66.77   293.97   |    -329.23    329.23   |          35.26
    4 -329.23   35.26      .00   |    -329.23       .00   |            .00
    To keep it short (I'm a 1 finger typer!), I'm using a loan of $1000 repayable
    over 4 years, at interest of 12% annually; payment works out to $329.23.

    The above can be quite easily programmed; it is in Basic anyway;
    I presume just as easy in Excel.

    *** as example, if borrower pays off at end of 2 years, a "rebate"
    of $102.03 is due if he remits $658.46; notice that this equals the
    interest left which is 66.77 + 35.26 = 102.03; see the 3rd and 4th
    payments in the section where interest is not precalculated.
    (or simply the difference between the 2 amounts owing: 658.46 - 556.43)

    I'll assume you'll follow what I did; if not, come back with questions.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Newbie
    Joined
    Dec 2011
    Posts
    3

    Re: Actuarial Loan Settlement equation

    Moo,

    Thanks for looking in on this....The constants will be monthly repayment, loan duration, term remaining and original loan amount.....Thanks.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Dec 2011
    Posts
    3

    Re: Actuarial Loan Settlement equation

    Thanks Wilmer....Most helpful.
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie
    Joined
    May 2013
    From
    uk
    Posts
    4

    Re: Actuarial Loan Settlement equation

    Hi Wilmer - can you help me understand how to program this,
    any chance you can post the "basic" code plz

    thanks Zima
    Follow Math Help Forum on Facebook and Google+

  7. #7
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,085
    Thanks
    67

    Re: Actuarial Loan Settlement equation

    Zima, you're posting on a thread that's about 18 months inactive.

    Start your own thread: then it won't get lost!

    Let me know what it is you'd like an example of in Basic;
    calculate a loan payment?

    Post a FULL PROBLEM.
    Follow Math Help Forum on Facebook and Google+

  8. #8
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,085
    Thanks
    67

    Re: Actuarial Loan Settlement equation

    Code:
        Interest calculated        Interest precalculated   Refund if precalculated 
    0                  1000.00   |              1316.92   |         316.92
    1 -329.23  120.00   790.77   |    -329.23    987.69   |         196.92
    2 -329.23   94.89   556.43   |    -329.23    658.46   |         102.03 ***
    3 -329.23   66.77   293.97   |    -329.23    329.23   |          35.26
    4 -329.23   35.26      .00   |    -329.23       .00   |            .00
    Well, you seem to be asking for the coding in Basic that produced the above.

    It's assumed that loan amount (1000.00) and the payment (329.23) are givens;
    also the number of payments (4) and the rate (12% = .12).

    100 a=1000 : p=329.23 : n=4 : r = .12

    110 b=p*n [that'll be the 1316.92 above]

    120 FOR k = 1 TO n [means loop n times]

    130 i=a*r [calculates actual interest at each payment]

    140 a=a-p+i [updates actual loan balance]

    150 b=b-p [updates precalculated loan balance above]

    160 d=b-a [updates refund column above]

    170 PRINT k,p,i,a,p,b,d [prints out as shown in above]

    180 NEXT k

    That's it.

    I'll REPEAT: this assumes refunds are given ONLY on last day of month.
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Newbie
    Joined
    May 2013
    From
    uk
    Posts
    4

    Re: Actuarial Loan Settlement equation

    Hi - thank you for posting this, am playing with the numbers now
    an example of what i'm trying to achieve is on this website Settlement Calculator
    Follow Math Help Forum on Facebook and Google+

  10. #10
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,085
    Thanks
    67

    Re: Actuarial Loan Settlement equation

    At this link (given at start of this thread):
    The Consumer Credit (Early Settlement) Regulations 2004
    I agree with the balances owing of 3984.00 (12th month) and 3893.17 (13th month).
    Shown is settlement required after 12 payments = 4067.62.
    This calculation assumes 28 out of 30 days.

    At the calculator link you're using, required is 4068.91.

    Negligible difference. Can't tell what is used:
    are they averaging 30day and 31day months?

    All I can do is suggest following; these would be the "givens":
    a = amount of loan (5000)
    r = APR (14%)
    p = monthly payment = 134.57
    n = number of payments before settlement (12)
    d = days in month (30) : could also be 28 (Feb) or 31

    Step 1 (get monthly rate i):
    Formula: i = (1 + r/100)^(1/12) - 1
    so: i = 1.14^1/12 - 1 = ~.0109788

    Step 2 (get loan balance b at month n+1):
    Formula: b = a(1+i)^(n+1) - p[(1+i)^(n+1)-1]/i
    so: b = 5000(1.0109788)^13 - 134.57(1.01090788^13 - 1) / .01090788 = 3893.17

    Step 3 (calculate settlement amount s)
    Formula: s = b(1+i)^(28/d) + p
    so: s = 3893.17(1.01090788)^(28/30) + 134.57 = 4067.62

    Hope that helps...bonne chance
    Thanks from zima101
    Follow Math Help Forum on Facebook and Google+

  11. #11
    Newbie
    Joined
    May 2013
    From
    uk
    Posts
    4

    Re: Actuarial Loan Settlement equation

    Hi Wilmer - thank you, very much appreciated
    Follow Math Help Forum on Facebook and Google+

  12. #12
    Newbie
    Joined
    May 2013
    From
    uk
    Posts
    4

    Re: Actuarial Loan Settlement equation

    Hi, have been running the equation with some "real" data
    and an issue im having is that the rate I have makes the settlement figure different from the calculator at financecalcs.co.uk
    I notice this calculator does not need you to enter a rate or APR.
    Is it possible to adjust the equation to mirror this?

    thankyou in advance MG
    p.s can start a new thread if preferred
    Follow Math Help Forum on Facebook and Google+

  13. #13
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,085
    Thanks
    67

    Re: Actuarial Loan Settlement equation

    I'll PM you on this.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. actuarial exam question
    Posted in the Advanced Statistics Forum
    Replies: 3
    Last Post: October 7th 2011, 10:01 PM
  2. actuarial mathematics
    Posted in the Advanced Applied Math Forum
    Replies: 0
    Last Post: May 26th 2009, 10:51 PM
  3. Actuarial PV
    Posted in the Business Math Forum
    Replies: 1
    Last Post: May 3rd 2009, 06:17 PM
  4. Finance/Actuarial question. Please help!
    Posted in the Business Math Forum
    Replies: 18
    Last Post: April 11th 2008, 06:36 AM
  5. Structured Settlement Vs. Lump Sum Settlement
    Posted in the Business Math Forum
    Replies: 2
    Last Post: June 5th 2005, 04:47 PM

Search Tags


/mathhelpforum @mathhelpforum