which financial function? (excel 97 sr2)

Topic

New Reply

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

Reply | Quote

Replies

Try the FV function (one line)

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

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote