Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    which financial function? (excel 97 sr2)

    I'm in the financial services industry and as an enhancement of member care, I'm seeking to mechanise our 'arrears analysis' procedures.
    I haven't found the appropriate Excel function to enable us to assess which borrowers are behind, or ahead of, their promised repayment schedules, and by how much.
    The parameters are loan_amount, loan_date, interest_rate, promised_repayment (all equal), repayment_interval, and 'today' (or a specified future date, like when the member might get the letter of phone call bearing the good news.)
    From 'today,' the loandate, and the interval, excel can work out how many payments, n, should have happened.
    What I want is the function involving n and the others which describes (or predicts) what the 'present' loan balance should be - or, in general, would be - after n repayments, if all had been made exactly on schedule. With data from the accounts package, I can then work out the shortfall.
    I thought the function might be =PV but it's not. =CUMPRINC does something vaguely similar, but isn't what I want.
    If there's a racing model of the one I do want, resilient to situations where the first repayment is not exactly one interval after the loandate, that would be a bonus.
    Thanks
    John Rose, Secretary, Handsworth Breakthrough Credit Union Ltd
    (Authorised and Regulated by the Financial Services Authority, Reg. No. 213302)
    Birmingham

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: which financial function? (excel 97 sr2)

    Try the FV function (one line)

    =FV(interest_rate/repayment_interval,(NOW()-loan_date)/365*repayment_interval,-promised_repayment,loan_amount,0)

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: which financial function? (excel 97 sr2)

    John, here are some thoughts: NPER will help you derive the number of periods, DATEDIF using "m" months parameter will tell you how many months have elapsed from start date, then you can compare count of payments-made to elapsed payment periods. You'll need a variation on CUMPRIN to derive the Principal which should have been paid.

    Do you want specific formulas, or do you want to have a go at it yourself? If you search back in this Excel Forum you'll find some variations on repayment schedules, to get you started. It would help if you could model exactly what you are looking for on a Worksheet and attach it to your original post using the Edit button.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: which financial function? (excel 97 sr2)

    Thank you both very much. Have now got it cracked. As the sample says, for 'future' read 'now', and for 'present' read 'past'. At least as far as the Future Value function is concerned.
    Attached Files Attached Files

Posting Permissions

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