How to calculate closing costs using APR and rate?

[itfmm]

I'm trying to build a function to calculate the closing costs associated with certain loan parameters. Example here is 30yr loan, note rate is 4.125, APR is 4.198, loan amount $200,000. I can figure out the APR using the =RATE function, having the total closing costs already on hand. In this case, the closing costs are .875% of the loan. What I can't seem to find anywhere is how to get the total amount of closing costs knowing the rate and APR, assuming the loan amount is always $200k and 30yr term. Below is my APR calculation formula:

=(RATE(360,PMT(0.04125/12,360,200000),(200000-(200000*0.00875)))*12)*100

Again, I need a function to calculate the ".00875," knowing the APR.

Thanks so much!

 

[joeu2004]

I'm trying to build a function to calculate the closing costs associated with certain loan parameters. Example here is 30yr loan, note rate is 4.125, APR is 4.198, loan amount $200,000. I can figure out the APR using the =RATE function, having the total closing costs already on hand. In this case, the closing costs are .875% of the loan.
[....]
=(RATE(360,PMT(0.04125/12,360,200000),(200000-(200000*0.00875)))*12)*100

[...] I need a function to calculate the ".00875," knowing the APR.

Note: I would calculate just RATE(...)*12 and format as Percentage instead of multiplying by 100.

First, the APR is closer to 4.1985%. If you use 4.198% or even 4.199% (more correct), the calculated closing cost percentage will be 0.8694% or 0.8813% respectively, not very close to the expected 0.8750%.

The closing cost percentage is:

=1 - PV(4.1985%/12,360,PMT(4.125%/12,360,200000))/200000

formatted as Percentage preferably with at least 4 percentage decimal places.

The result is about 0.8754% because even 4.1985% is not the "exact" APR that you would calculate using 0.8750%. Your original formula returns about 4.19847%.