Share
Explore BrainMass

Blended Interest Rate

Make a usable spreadsheet that can calculate the blended interest rate for 2 separate loans. Below is an example of the output data that this spreadsheet would produce for this particular scenario:

Mortgage Loan Summary
Loan one amount $250,000.00
Interest Rate 5.25%
Term 30 years
Monthly payment $1,093.75 Interest Only
Loan two amount $160,000.00
Interest Rate 7.00%
Term 30 years
Monthly payment $933.33 Interest Only
Total amount financed $410,000.00
Total monthly payment $2,027.08
Blended interest rate 5.933%

I need a simple excel tool in which one may enter the amount and interest rate for two separate loans and amounts that calculates the blended interest rate for those two "interest-Only" loan amounts.

Solution Preview

I have attached the required spread sheet with this.

To calculate the monthly interest amount, we use the formula,

Interest = Principal Amount * Number of years * rate of interest/100

Since we are finding the monthly interest, the above formula changes to

Interest amount = Principal Amount * (1/12) * rate of interest/100 -------- (1)

1/12 ==> for one month, we in effect ...

Solution Summary

This shows how to create an excel spreadsheet to calculate blended interest rate.

$2.19