Apr With Pmi |
Apr With Pmi - Excel |
|
A1 Loan Amount: $250,000
B1 Closing fees: $5,000
C1 Rate: 6.25%
D1 Term: 360 months
E1 Payment: $1,539.29 (w/o PMI)
F1 PMI: $108
I can calculate APR that does not take PMI into consideration accurately (enough) using:
=RATE(D1,-E1,A1-B1)*12
However, I don't know how to calculate the PMI into the formula. The PMI will cease to be necessary at 80% of the original loan value (A1) thus changing the monthly payment from $1,646.29 to $1,539.29
Any ideas or suggestions?
Thanks
Similar Topics
so I thought I would try again.
Does anyone know of an Excel template--or a small stand-alone
program--that will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or low-cost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Received Date & Time: 10/29/2010 3:27:00 PM
Completed Date & Time: 11/1/2010 3:57:32 AM
Business Hours: 08:00 AM to 05:00 PM
Non Business hours: 05:00 PM to 08:00 AM
Weekdays : Monday to Friday
Weekends: Saturday & Sunday
If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.
In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.
Please let me know if you need any more details
It looks like this:
=(E3/((A3+D3)/2))*12/months
So if I substitute with some actual data:
=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).
What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 -46 432 -92 -616 -1140 -1664 -2392 -3120 -3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
If any one could give me a solution for this its will be a great help for me.
I am working in a bank and on every day we receive Month to date data of Loans and advances made by every branch with Region wise total and manager wise total. With Sum of loan and count of loan. In our MIS format the in A coulum branches are sorted and listed in a sequence with sub total Region. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format.
Thanks.
I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:
01/01/2007
02/01/2007
03/01/2007
I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.
Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!
Many thanks,
Caitlin
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.
The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.
I've seen this before, but this time, I need to calculate many
thousands of cells and don't have time for this workaround.
Any ideas?
Thanks.
Don S
=DATEDIF(E21,TODAY(),"y")&" y "&DATEDIF(E21,TODAY(),"ym")&" m"
Thanks
Rick
I can calculate the formula for this , but the problem comes where for eg there is a loss in Jan 2008 for -95415 and there was a profit in Jan 2007 for 80215. How do I set up formula to take negatives & positives into account in calculating the increase or decrease/
Your assistance or anybody's on the forum will be greatly appreciated
Regards
Howard
http://www.mrexcel.com/forum/showthr...ghlight=howardneed
I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:
Column M - Estimated Delivery dates
Column N - Actual Delivery dates
Column O - =IF(SUM(M2-N2)>0,1,0)
Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))
This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?
Any help much appreciated
Thanks,
James
can calculate overtime hours. In california over 8 hrs in one day is
overtime. The 8 would be considered regular hours and anything over is OT.
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00
How would you formulate an equation to determine the duration of time or
differnce between the start and end time?
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc
I need help....I am working production scheduling with Start and End time but there is a lunch time and tea time. Is there any formula to calculate Start time to End time with excluding lunch time?
Here is example :
Lunch time :12:00pm to 1:00pm.
Duration Process :6 hours.
Start Time (8:00am)
End time (5:00 pm)
I need a formula to calculate from Start Time 8:00am and what is the End Time? with 1 hour lunch time...
Thanks,
ET
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
Cost = B1
Margin = C1
I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?
Alex
There is about 20 staffs in our department and I have made a spreadsheet with 12 tabs for each month of the year Jan-Dec. If each staff have 15 days annual leave each year and how can I make a formular that can automatically calculate the balance of annual leave and entered in the tabs of the following months. For example if staff A used 5 days in Jan how can the balance of 10days be entered in the tabs from Feb-Dec. If he use another 5 days in July the remain balance of 5 days be automatically entered in the tab from Aug to Dec. Likewise if staff A took 2 days sick leave in Jan, and 2 days in Mar how can this sick days be automatically sum up in the months after. I am doing this manually at the moment and it is very time consuming and also very easy to make mistake.
Thanks and sorry for this long questions
Can you please help me,
A1= time in
B1= time out
C1= time in
D1= time out
I want to calculate the late and under time,
Office start at 9:am w/30 mins Grace period,
The break time is one hour only, please include over breaktime in calculation.
End of office hours 6:00 pm, strickly no over time
On excel, if I divide 462,534.05 by 335 I get 1,380.70. Then if I times 1,380.70 by 335 I get the SAME result of 462,534.05.
I have to account for the difference of not dividing equally so there is some format or accounting function that's preset that I don't want but I have no idea how to fix it or format my excel sheet to calculate as my calculator would.
Please can someone help? Thanks!