Forum Discussion

Lsingh718's avatar
Lsingh718
Copper Contributor
Aug 08, 2022

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

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 roo...
excel
Formulas and Functions
Singh7181425's avatar
Singh7181425
Copper Contributor
Aug 16, 2022
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.
Jagodragon's avatar
Jagodragon
Iron Contributor
Aug 17, 2022
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
  • Singh7181425's avatar
    Singh7181425
    Copper Contributor
    Aug 17, 2022

    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:

     

    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor
      Aug 22, 2022

      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.

       

       

      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).

       

       

    • Jagodragon's avatar
      Jagodragon
      Iron Contributor
      Aug 22, 2022

       

      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.

Resources