Calculating Total Delinquent Interest for Adjustable Rate Loans

[biscuit_bytes]

Hi, I'm working on a project for my accounting class. Basically, I'm calculating total delinquent interest for several loans in default. The template looks like this:

 

[biscuit_bytes]

ok, I did not mean to prematurely post the thread. And now I can't figure out how to edit my post, lol.

Anyway, I have a function in column F that calculates total delinquent interest. It looks like this:

_{=((YEAR(A2)-YEAR(E2))*12+MONTH(A2)-MONTH(E2))*(C2*D2/12)}

Column A represents the current accounting cycle, Column E was the date of the last paid installment.

My issue pertains to adjustable rate loans. I have several loans that I need to calculate delinquent interests for, but these loans have several rate changes that I have organized in another spreadsheet. What would be the most efficient and accurate way to calculate the total delinquent interest for these loans?

I really wouldn't want to have to sit and make simple tables for each loan to calculate total delinquent interest for all of the loans. That would be terrible.

A function doesn't seem feasible, or does it?

Is there a macro that I can use to accomplish this?

Thank you in advance.

 

[biscuit_bytes]

Bump. Hoping someone will be able to help. :]

 

[biscuit_bytes]

Bump.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Cycle	ID	UPB	Rate	Last	Interest
2	15-Jan-17	000032	410,071.00	3.78%	1-Feb-15	29,709.64
3	15-Jan-17	000032	410,071.00	3.78%	1-Feb-15	29,709.64
4	15-Jan-17	000032	410,071.00	3.78%	1-Feb-15	30,762.14
5
Interest

Cell Formulas
Range	Formula
F2	=((YEAR(A2)-YEAR(E2))*12+MONTH(A2)-MONTH(E2))*(C2*D2/12)
F3	=DATEDIF(E3,A3,"m")*C3*D3/12
F4	=FV(D4/12,DATEDIF(E4,A4,"m"),0,-C4)-C4

Do the rate changes occur once per year?
Are the rate changes unique to particular loans?
Does the interest compound annually?
How did you calculate the amount shown for late interest?

 

[Dave Patton]

You could also check Excel's Help for FVSchedule.

 

[biscuit_bytes]

Hi Dave! First of all, thanks for responding!

Interest changes occur once a year. Rate changes only occur on certain loans; I'm able to identify those separately.
Interest does not compound on these loans per the investor's requirements (according to the assignment).

In this assignment, we're calculating interest on a scheduled basis. It does not compound.

If the borrower defaulted on 5/1/2012 and the UPB at the time was 345,000.00 with an interest rate of 3.25%, then the total delinquency should be:

Total Delinquent Interest = (345,000 * 3.25%/12) * number of delinquent months (from default date to month of 1/1/2017).

As you've shown, I have formula options to correctly calculate total delinquent interest. My problem arises with loans in which I have a different interest rate every year. I'm not sure what would be the most efficient way to calculate delinquent interest for loans with several interest rates (some loans in this project are 3-5 years delinquent).

With that being said, FVSchedule could possibly work, but FVSchedule will just calculate total interest for a 12 month period. Is there a way I can tweak the function to calculate interest while taking the default date, current cycle date, and also consider the effective date for the interest rate changes? I'm going to guess no, but I'm just a beginner

 

[Dave Patton]

How did you calculate the amount shown for late interest? See your post #1; the amount is different than what the formulas show.

FVSchedule works for multiple years but it compounds the interest.

You can prepare a worksheet with a couple of sample cases where the rate changes.
- show how you would manually calculate the interest

We may be able to provide a formula once we see your example.

 

[biscuit_bytes]

Sorry, Dave, I was busy yesterday. I'll respond to your post as soon as I can. Thanks again!!