Advanced Loan Analysis

[jtbaby13]

Hi all,

I am attempting to do some financial analysis for a redevelopment project my company is currently working on - I say attempting because a lot of my modeling skills seem to have escaped me.

My first problem is a loan with a change in interest rate after year 5. So for the first 5 years, of a 25 year term, the rate will be 4% but will balloon to 5% or 6% after year 5 and for the life of the loan; or until a refi.

My second problem is trying to analyze a portfolio of 4 different loan type to figure out the optimal balance of monies - say $15 million. The loans are:

1. (previously mentioned) 4% rate for first 5 years of a 25 year term; with rate ballooning to 5% or 6%. Lets say 6% for this scenario.

2. Rate - 3.7% for 10 years

3. Rate 4.2% for 25 years, max of $5 million.

4. Rate 1.0% for 15 years, max $2.25 million

There is a lot of changing variables here which clearly makes this more complex. Any help you can provide is very much appreciated, especially with concern to my first question of analyzing a loan with a change in interest rate.

Thanks,
JT

 

[Akashwani]

Hi JT,

Take a look here........

excelisfun -- Excel How To Videos - YouTube

Type Loan (or Interest) into the search box, Mike has loads of videos on loans and business maths.

I hope that helps.

Good luck.

Ak

 

[Firefly2012]

In regards to your first query, I hope the below helps. It is how I might approach the question of what would the payments be given known rates/total term:

Excel 2010
	A	B	C
1	Assumptions
2	Initial Loan	$15,000,000.00
3	Initial Rate	4%
4	Initial Term	5	years
5	Total Term	25	years
6	Initial Payments	-$79,175.53
7	Balance After Initial Term	$13,065,692.44
8	Remaining Term	20
9	Follow on rate	5%
10	New Payments	-$86,227.79
Sheet1

Cell Formulas
Range	Formula
B6	=PMT(B3/12,B5*12,B2)
B7	=B2+CUMPRINC(B3/12,B5*12,B2,1,B4*12,0)
B8	=B5-B4
B10	=PMT(B9/12,B8*12,B7)

 

[jtbaby13]

Thank you Firefly2012 and Akashwani. Both replies are very useful.

Much appreciated!