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

Question

Hello,&nbsp;I was hoping someone can help me figure out an excel formula to calculate a loan amount that considers a fee and a cap.&nbsp; 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).&nbsp; 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.&nbsp;

jagodragon · Answer

Lsingh718&nbsp;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.&nbsp;

jagodragon · Answer

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

joeuser2004 · Answer

Jagodragon&nbsp; wrote:&nbsp; ``It looks like that is using hidden fees to calculate that number``&nbsp;Not at all.&nbsp; The calculator is simply a poor presentation of the separate solutions to two problems, as it says (correcting grammar):&nbsp; ``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``.&nbsp;Perhaps the following presentation will be clear.&nbsp;&nbsp;Aside.... The calculator states (correction grammar):&nbsp; ``Rounded to nearest dollar -- actual amounts might vary due to rounding``.&nbsp;I purposely do not round because:&nbsp; (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).&nbsp;I wanted you to clearly see the origin of 26103 and 1103 (sic).&nbsp;&nbsp;

singh7181425 · Answer

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.

singh7181425 · Answer

JagodragonHi 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:&nbsp;

Forum Discussion

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

6 Replies

Resources