MS Excel Formula Help

[lee_hdi]

Hi,
I'm trying to create a spreadsheet that calculates loan repayments per month, but I also want it to calculate the interest applied to the arrears; but I've hit a brick wall.

Basically, I want excel to calculate the interest applied to the arrears amount from the date of the missed payment to today's date. However, I've no idea how to go about writing the formula.

I think it might be a Vlookup, like looks for the word "missed" and then returns the relevant date, but I can't get it to work.



I've attached a copy of the excel spreadsheet so that you can look.

Excel 97-2003 https://dl.dropbox.com/u/54352977/Loan_repayments.xls

Excel 2010 - https://dl.dropbox.com/u/54352977/Loan_repayments.xlsx

Any help would be appreciated

Thanks

[Cod3waX]

this is a sum to make a running total of arrears

=SUM(I$2:$I99) change the I to the letter of ur Arrears

[lee_hdi]

this is a sum to make a running total of arrears

=SUM(I$2:$I99) change the I to the letter of ur Arrears

That would just add up the "Ending Balance" column

I need it to detect when the first "Missed Payment" is select, then calculate the interest from there.

E.g if the first missed payment is 01/01/2012, then I need it to work out the interest from that date to today

Thanks

[masur123]

=(TODAY()-B20)*((K20*Interest_Rate)/365)

Is it something like this? If you post this in each cell in L?

[lee_hdi]

Think I've figured it out

Rearranged the columns so that Paid/Missed is in column A. Then I used Vlookup to look for "missed" and it will retrieve the corresponding date. Then I used =TODAY()-that date to give the number of days

[lee_hdi]

Think this seems ok

Excel 97-2003 https://dl.dropbox.com/u/54352977/Loan_repayments.xls

Excel 2010 - https://dl.dropbox.com/u/54352977/Loan_repayments.xlsx

[masur123]

Sorry, maybe I misunderstood the question, I thought you were looking for the interest amount on each individual payment, and not the interest with the arrears.

[masur123]

The vlookup, by the way will only return the first instance of missed payment and no more than that.

[lee_hdi]

They should start paying interest on the arrears from the first missed payment, and then the interested is applied on the total arrears

I think anyway.

Going to compare it to my friends when he manually calculates the total arrears

It's for work, basically to try and save some time

[masur123]

ahh so basically you are just adding back missed payment to the total arrears and recalculating the interest?

[lee_hdi]

Yeah. So like in the example below. The person missed payments in May and June. Then caught up on the arrears in august, so the arrears were initially reset to ?0. Then he missed another payment in November, and so the interest has been re-applied to the ?212.47 that he is in arrears

[XRay333]

Excel PDFs

I've uped some helpful PDFs (Excel for Dumbies) for another request. There maybe some helpful books in there for you.

https://hotfile.com/dl/184628115/c46d2f2/Excel.rar.html

Hope this helps.