Closed Thread
Results 1 to 8 of 8

Calculate First Loan PMT when Annual Escalation applicable

  1. #1
    Registered User
    Join Date
    07-26-2016
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Calculate First Loan PMT when Annual Escalation applicable

    Hi,

    Can anyone maybe help me with a formula to calculate the initial payment on the following transaction please:
    Capital: R41 800 000,
    Annual interest rate: 14.5%
    Type: In Arrears
    Annual Escalation: 10%
    Term: 60 months
    Compound: Daily

    I need a formula to calculate the initial PMT’s keeping in mind the Term must remain the same (60mths)
    1) Monthly?
    2) Quarterly?

    My “bank” calculated the initial payments but they are “not able” to give or tell me how
    1) First month pmt = R825570
    2) First Quarterly pmt = R2 622 500

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Hi there,

    Does the Annual Escalation Rate apply to the Repayment Amount?

    I.e. if you repay R1.0 M per month for the first twelve months, do you repay R1.1 M for the next twelve months?

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    07-26-2016
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Hello Greg,

    Yes, the annual escalation must be applied to the repayment amount after the first 12 months. The problem is that the loan will now be paid off over a shorter term. The only way to prevent this is to start the first 12 months at a lower premium and then escalate.
    In other words payment must start at a lower premium and escalate annually to eventually pay off the R41 800 000 capital amount over the same term (60 months).

    Due to interest implications (because of the reduced premium) the total repayment amount will be higher than the total repayments for a loan with constant payment. I have tried to keep the total repayment amount the same but it is not giving me the correct initial premium as indicated by the bank.


    Kind regards

    Flip

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Hi again Flip,

    According to my calculations in the attached workbook, your initial repayments should be:

    Monthly : R829,611.27


    Quarterly : R2,519,147.98


    At least you can have fun asking your "bank" to explain where my calculations are incorrect!


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 07-26-2016 at 07:30 PM. Reason: Minor cosmetic changes to attachment

  5. #5
    Registered User
    Join Date
    07-26-2016
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Good morning Greg,

    Thank you very much for your response and assistance. I will let you know what the outcome is!

    Regards

    Flip

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Hi Flip,


    I will let you know what the outcome is!


    Please do - I'll be very interested


    Regards,

    Greg M

  7. #7
    Registered User
    Join Date
    07-13-2021
    Location
    South Africa
    MS-Off Ver
    Office 365
    Posts
    1

    Re: Calculate First Loan PMT when Annual Escalation applicable

    I am looking for a formula to work out initial installment when:
    1. Period = 60 month
    2. Interest rate = constant
    3. Payments escalate every 12 months by fixed escalation rate
    4. PV remains the same
    5. FV = Zero

    Is there a formula that can calculate the first installment in excel?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Calculate First Loan PMT when Annual Escalation applicable

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 07-24-2016, 07:33 PM
  2. Replies: 1
    Last Post: 03-18-2015, 07:21 PM
  3. Replies: 1
    Last Post: 03-18-2015, 06:16 PM
  4. Replies: 3
    Last Post: 03-17-2015, 11:36 PM
  5. Calculate particular period rent with day vise escalation
    By rahulbhanwar in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 04-03-2012, 04:24 AM
  6. How to calculate Loan
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2010, 01:28 PM
  7. [SOLVED] calculate loan amout
    By dadddc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-18-2006, 10:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1