I have used the PMT function but this gives me the total to pay per month.
I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.
So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.
Is this possible or is this too complicated.
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine.
I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy.
What I have so far:
p*(1+(r/100))^n Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan?
Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!)
To keep it short i followed this guide for the amortization table.
and now I am so confused about if I should be using PMT, PPMT, NPER?!
I currently use goal seek to calculate an interest rate for a loan product. My problem is i would like to have the same function but not through a goal seek. In goal seek i have to set the value i want to achieve but ideally i want it to calculate automatically
I have attached a workbook with details. I use a loan amortization schedule to calculate the interest from parameters set on sheet 1
Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success
I have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for one-off expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.
I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. This might end with a one-off payment in a couple of months, I'd like to calculate what is owed there as a minimum.
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
I am writing a sports schedule or fixture list. The home teams are in column B and the away teams are in column C.
I want to prevent any team having 3 consecutive home matches. Is there any way a formula can be used to alert me if there are 3 consecutive home matches.
I owe 15462 in the bank, currency dont matter here, that is what I owe right now, but I want to have a cell in the frontpage with the amount left, so can I make a line called =remaining-each month
the amount should then each month be substracted from the new month and so on, until the amount is 0
can this be done?
the second page in the spreadsheet has a post with monthly pays to the bank ...
I have been searching Excel Help for a code that will allow me to program the Ctrl + Home function.
Specifically, I have a macro that leaves the selected cell too far down on the spreadsheet. Instead of making the user scroll up or press the Ctrl + Home keys, I want to write a code at the end of my existing macro.
On the computers in my office they have a My Documents folder that is in the directory C:Data. There is also a My Documents folder in the usual windows location C:Documents and Settings etc. Is there a function that will give me the path of the folder that is on the desktop? The macro will be run on computers with the folder in different locations which is why I need to look it up.
I'm working with a loan amortization worksheet (downloaded from Office Online). Unless I don't understand correctly, the date formula on the worksheet doesn't calculate the way I need it to. I'm not totally sure what the formula they use is doing. It does use a lot of named ranges.
If a user inserts the total "Number of Payments per year", then I want the date to return the proper payment date.
For instance, If the start date is 1/1/07 and the number of payments per year is 24 then the payment dates should be like
1/15/07 1/31/01 2/15/07 2/28/07
It should be the 15th and the last day of the month.
If I put 52 as the number of payments then I want the formula to set the payment dates to every Friday.
I'm still learning formulas so bare with me guys. Attached is the worksheet.
I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
I created a custom tab withthe UI editor into my personal sheet which is set as startup and I want to have this tab automaticly shown when it is opened.
So when the workbook is opened I would like the show the tab Andre rather than the home tab.
I have an older laptop with XP and 2007 Office / Excel this works, with the new laptop doesn't work?
basically - I open a new spreadsheet - blank - just basic
I used to be able to click the data tab, then click From Access, then enter my URL to an access database on my website and BAM my data would populate in cell A1
with VISTA - I get the same error over and over again, tried a different XP machine, no problem? What gives?
error box states: Microsoft Office Excel cannot access the file [url] there are several possible reasons.
the file name or path does not exist the file is being used workbook has the same name
I'm starting to really hate VISTA after about 3hrs with this new laptop!
Found the wonderful code here at Ozgrid and would like to change the return to home selection.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Range("d4:Ad104"), Target) Is Nothing Then Int_color = Target.Interior.ColorIndex Select Case Int_color Case xlNone 'Question, what does "xlNone" do? Target.Interior.ColorIndex = 3 Target.Font.ColorIndex = 3 Target.Value = "true" 'Range("A1").Select - would like to change this to move the selection to 'the present row clicked, column c. If I click on G9 the selection returns 'to C9. Case Else Target.Interior.ColorIndex = xlNone Target.Value = "false"
I have 4 Loans of various interest rates, balances, and minimum payments.
Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans?
Given: Total Monthly payment: M Interest Rate for each loan: R1, R2, R3, R4 Initial Principal for each Loan: P1, P2, P3, P4 Minimum Monthly Payment: Min1, Min2, Min3, Min4
Each month, how should I distribute M over the 4 loans?
There are various references and links to " mortgage calculators;" though they are specific to the US dollar. Is the formula still the same, irrespective of the currency and why does it come across as quite a complex calculation? i have been taksed with designing a "calculator" and don't seem to know where to start as the currency issue is confusing me.
So I'm looking for a formula that returns information to one cell. A formula that looks if in 'this' column you see the category code '1' for example, then return to me the money spent for ALL the 1's. It should return $17...
The table above is small just for demo purpose, my table has 8 category codes at the moment and the number of rows can be between 15 and 50 I guess... it's a monthly break down.
I would like to see how much is spent in total for each category...
In addition if possible, can this be done for two separate tables... We are two people, and I have two table recording expenditure.
Look up in both tables, search the two category columns for category '1' for example, then return all the $'s added together... So for Category 1 now it would show $49...
I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?
Here is the effective rate calcs on a random normal amortization loan:
this is in B2, and answer is 7% =RATE(B4*B5,-((B3+B7)/B6),B7)*12
B3 = Total*Interest 279017.8 B4 = #*Years*in*Loan 30 B5 = #*Payments*/*Year 12 B6 = Total*Payments 360 B7 = Beginning*Principal 200000 B8 - Ending*Balance 0 problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan.
Here is the example I'm working on... A client's loan is the following: Loan amount - 131,538 interest rate - 6.15 30 year amortization 10 years interest only
normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan.
When using 2013 Excel in the Home tab, line 1 is not visible. Using the File tab will show line one, but the Home title bar is unavailable. Toggling back and forth is not efficient. No one in this office has ever seen an Excel program not display a full page under the title bar in the Home tab.
how I can solve the issue of creating a spreadsheet (similar to an amortization one) that could deal with unequal re-payment regime as well as unfixed (anytime of the month) payment periods.
I have a created a Data Chart Below. A - C are the columns and 1-7 are the rows. I have hard-coded the equation in cell B5 that I am using.
What I want to do is input an number into B3 that automatically makes Cell B5 equal to Cell C1. Is there a process in excel you can use to do this. Or do you just have to use trial and error?
A B C 1Loan Amount$10,500,000 0.0730041581143804 2Term 10 years 3Rate 4Amortization30 years 5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012 6Annual Payment$766,544 7Monthly Payment$63,879
I have created a excel sheet here i want the total interest charged for three months in 3rd mnth interest charged column, if i select 7 mnths term total interest charged for 7 months should come in 7th month interest charged colum, if it is 13 months total interest for 12 months in 12th month interest column and remaining 1 month interest in 13th month interest charged column
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US) - £ English (UK) - € Euro (€ 123) - ¥ Chinese (PRC) - fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
