Need help with excel formula to calculate a loan amount considering fee

Copper Contributor

Hello,

 

I was hoping someone can help me figure out an excel formula to calculate a loan amount that considers a fee and a cap.  For the example below, a borrower requests $65K, they only have room in their budget to receive 30K for the year, they are charged an origination fee of 4.228% for the loan which gets added to their budget (total amount they can receive for that year).  I'm trying to figure out a formula that calculates the maximum loan amount that they can receive in that first year without going over the budget (Cell B2 +B4), the remaining loan amount gets transferred to the following year.

 

Screen Shot 2022-08-08 at 10.43.01 AM.png

6 Replies

@Lsingh718 

I am assuming that the Total of B4 and B5 needs to equal B2. this means that your value in B4 needs to come from B5 and not B2. Please see the screen shot below. Formulas are on the far right of the image.

 

EXCEL_lybXhFI40A.png

Thank you Jagodragon for responding. This helps, I may have overlooked mentioning that the loan origination fee amount (B4) gets added to B2, this increases the total amount that a person can borrow in B2. I am still stuck on trying to figure out a formula to increase B5 up to the max so it does not go over B2 Plus B4. Not sure if this makes sense....it's been a long day.
b5 is showing what the loan is after the fee is removed. if you add the fee (B4) to the loan amount (B5) you get the max of 30,000.

B5 is showing the usable loan that can go to the customer after fee is deducted

@Jagodragon

Hi Robert, I may not have worded my question accurately, but I'm essentially trying to figure out an excel formula that calculates the highlighted cell below:

Singh7181425_0-1660767059772.png

 

 

@Singh7181425 

 

It looks like that is using hidden fees to calculate that number.

there is a 12.08472% total fees between loan+fees and amount dispersed. with out knowing what is making the large difference between 4.228 and 12.085, there is no way to calculate the correct answer.

this is because (i have to assume) the hidden fees are a mix of % and base fees.

the formulas i gave you previously would be correct if not for the hidden fees.

@Jagodragon  wrote:  ``It looks like that is using hidden fees to calculate that number``

 

Not at all.  The calculator is simply a poor presentation of the separate solutions to two problems, as it says (correcting grammar):  ``either the amount of money that you will receive after fees are applied or the increased amount that you need to request to cover the origination fees``.

 

Perhaps the following presentation will be clear.

 

JoeUser_0-1661195104714.png

 

Aside.... The calculator states (correction grammar):  ``Rounded to nearest dollar -- actual amounts might vary due to rounding``.

 

I purposely do not round because:  (1) it is unclear when rounding should occur (each calculation, or just for appearances); and (2) I disagree with their rounded results (I would say 26104 and 1104, not 26103 and 1103).

 

I wanted you to clearly see the origin of 26103 and 1103 (sic).