Aug 08 2022 10:33 AM
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.
Aug 08 2022 12:37 PM
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.
Aug 16 2022 04:20 PM
Aug 17 2022 06:30 AM
Aug 17 2022 01:14 PM
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:
Aug 22 2022 10:14 AM
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.
Aug 22 2022 12:16 PM
@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).