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 ...
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
View 9 Replies View RelatedIn 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?
need a formula that will give me a balance at the end, needs 2 columns with charges and credits and it allways give correct balance total on last column. have not used Excel for long time and forgot.
View 5 Replies View Related1. Want to findout in which stage the loan is ? Eg: 1212321231 is in PROCESSING STAGE because the date appears under processing .
2. want to find out how many stages it has paased ? Eg: Loan 1212321231 has passed 3 stages and now in 4th stage(processing).
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 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?
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.
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.
View 7 Replies View RelatedI have a file which student Monthly Loan Payment schedule.
I want to make a list which shows all payment period by installments and payment amount , dates by Student names.
Attached file, you can see all detail and wanted LIST.
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
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.
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.
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.
View 9 Replies View RelatedI 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 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
Example is attached.
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
INTEREST CAL.xlsx
With the data table given below, how can I formulate the yearly installment based on the tenure. Like below example.
Name
Loan
Yrly Pymnt
No. of Tenures
Y2014
Y2015
[Code] ........
I have uploaded a sample amortization schedule.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.
2. Also, if a value is entered in column E, then i require the whole table to update as well.
I have a running balance that works beautifully in my spreadsheet, but I cannot get it to display my balance on a line above my spreadsheet. I have attached a copy of my spreadsheet to make understanding my question easier. I have a sell price that will remain the same and as the bill is paid the balance should work itself down to $0. Currently I have three deductions in my spreadsheet but the balance only shows the first deduction.
View 4 Replies View RelatedHow to filter Dr and Cr Balance from attached sheet.
FILTER.xls
See attached workbook which is a stock order workbook with a summary re-order sheet -
The problem I have is that as the running balance effects the re-order column the summary re-order sheet will re-order from a the date that stock is needed onwards until someone types in stock recieved to get the balance right.
I need another condition within the summary sheet sumproduct formula so that the order will only go through once and not be repeated until a new figure is added into the number issued column on a future date.
I have to balance workload equally for everyone (Name: abc, xyz, mno) and New Orders must be shared or distributed equally for every person. New Orders must be shared in such a way that every person's percentage share must be made equal by distributing or sharing New Orders. Find an attachment named issue.xls
View 2 Replies View RelatedI am using excel 2007. I am working on an account register. I am using my spreadsheet to track when deposits and withdraws are made, when bills are paid, and when check have cleared my bank. I am using condintional formating to black out rows when bills have been paid, checks have cleare, and when withdraws have been made.
I only have one issue. I am tracking my current balance at the bottom of my spreadsheet I want to be able to go back and delete all of blacked out rows once a week without losing the value that was contained within them thus leaving the current balance unchanged.
i.e
On Monday the 17 check number 2207 clears my bank so in comumn “C” enter the word “cleared” in row 210 the row then turns black and everything is fine. My current balance now is $1,678.25, at the end of the month I want to be able to go back and delete all of the blacked out rows. So I go to row 210 and remove the row but now my balance changes back to what it was before that value was entered.
I want to know is there is a way to leave the balance unchanged even after I have deleted that row?
I need to sum the values of a column based on multiple criteria. i.e. I would like to sum the column "End Balance" based on both "Company"='B' and the "GL CODE" > 4404...... and < 4406.....
View 2 Replies View RelatedI would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
Ref attached sheet
I m facing a big and urgent problem in my worksheet..i have done my all work here but now i knew that there is a difference in this vb code..
I need your urgent favour to rectify it for me..
What i need is sheet1 is my journal jounal (data) and sheet2 is my reporting place where i set a macro button at top to update the report..
But in sheet2 column I i need an auto Balance of debit & credit..
Dont know why, but I get #REF! error. Enter this formula on a blanc sheet, say in A1
View 2 Replies View RelatedI need to Count the Balance Numbers . if Textbox1 value is 5 then show the Textbox2 zero.after saving textbox2 value is showing 4 and next time 3 .....0
View 6 Replies View RelatedI have excel data like from b3 to b35 there are different dates with different months and years in long date format and from G3 to G35 expenses in accounting format. what i need is the ONLY last month expense in another worksheet.
View 1 Replies View Related