Free Microsoft Excel 2013 Quick Reference

Loan amortization schedule should have subtotals for each year

Recently I used the amortization schedule designed by Microsoft. It's
fantastic. However, it will be more friendly and convenient for the users if
at the end of each year the user can see a subtotal for the interest paid,
principal paid, etc.

Actually, I tried to get those subtotals by inserting lines under the last
payment of each year, however that will result in different amounts of the
cumulative interest colum mainly becasue of the default set formulas.

Hopefully, we can see the improvement soon.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions


Post your answer or comment

comments powered by Disqus
I'd like to compute the NPV of many loans without running amortization schedules. I have the basic inputs, and currently drop them into a full amortization schedule, spanning up to 30 years. Thereafter, I sum the NPV of each individual payment. Can anyone suggest a macro or array formula that would crunch these numbers? Since I have so many to run, the current process is not at all efficient.

Thanks for any and all replies.

Is there a template for a Loan amortization Schedule where I can change the
interest rate each time "prime" changes and there is only one payment on June
1st of each year for 6 years

Is there a template for a Loan amortization Schedule where I can change the
interest rate each time "prime" changes and there is only one payment on June
1st of each year for 6 years

I downloaded the loan amortization schedule from the help function in Excel.
The template uses a condition in its IF formulas that requires a number of
cells be populated in order for the formula to run through its calculation.
An example is =IF(Values_Entered,A316+1,"") where cells d5-d7 and d9 had to
be populated or the cell is left blank. Was wondering how that is set up
where the IF formula looks to those cells to verify that there is data.
Also, "Values_Entered" does not appear in the drop box for the range names.
Thanks in advance for your assistance.

I was looking at the Loan Amortization Schedule downloaded at
http://office.microsoft.com/en-us/te...197771033.aspx and found
that the Payment Dates won't calculate for 24 payments per year (e.g. 1st and
15th payments). In stead of 1/1/2005 followed by 1/15/2005 I get 1/1/2005
followed by 2/1/2005 followed by another 2/1/2005. I believe the error stems
from the "DAY(Loan_Start)" portion from the formula, but it could come from
"(Pay_Num)*12/Num_Pmt_Per_Year" yielding 0.5 payment increments.

The original formula is:
=IF(Pay_Num"",DATE(YEAR(Loan_Start),MONTH(Loan_S tart)+(Pay_Num)*12/Num_Pmt_Per_Year,DAY(Loan_Start)),"")

I downloaded the loan amortization schedule from the help function in Excel.
The template uses a condition in its IF formulas that requires a number of
cells be populated in order for the formula to run through its calculation.
An example is =IF(Values_Entered,A316+1,"") where cells d5-d7 and d9 had to
be populated or the cell is left blank. Was wondering how that is set up
where the IF formula looks to those cells to verify that there is data.
Also, "Values_Entered" does not appear in the drop box for the range names.
Thanks in advance for your assistance.

I need to combine and analyze many loan amortization schedules, each starting
at a different date, AS OF the same date. I need total PV, Total Interest
paid during the year. Any ideas ?

I haven't been able to find a loan amortization schedule based on a 360-day
year. Does anyone know where I can get a template for this?

This is my Data:

month (Multiple Items)

Values
Row Sum of Net value The Growth Rate
2007 14,378,194
2008 2,762,771 #DIV/0!
2009 3,595,793 #DIV/0!
2010 624,715 #DIV/0!
Grand Total 21361473.11

I am trying to calculate the growth rate for each year by doing the following:

Value field settings ----> summarized by (sum) ----> show value as ----> % difference from (Base field:year,Base Itemrevious)

But I keep getting the error shown in the table above DIV/0

I guess because the fist year GR equals zero.
I don't how to overcome this problem.
And I don't know if this is the right way to calculate the Growth Rate to start with.

Thanks.

I have a loan amortization schedule that I know I am making harder than it needs to be, but I can't figure out how to make it easier. I have an initial loan amount. We pay interest on the loan for the previous month on the 1st, but the principle amount of the loan increases on the 25th of each month. Thanks in advance for any help.

Kimberly

Hi

Im in the process of putting together a home finance system for my university project and one of the key requirements of the system is that the system should avoid the need for the user to interact directly with the spreadsheet. So currently everything it does is through forms.....e.g. Add accounts, add transactions to accounts etc.

Now I need to somehow implement the loan amortization feature and I was wondering if its possible to somehow 'addin' a spreadsheet loan amortization schedule the system creates on to a form which the user can interact with? (instead of directly with the spreadsheet)

If this is not possible anyone got any others ideas on how it might be possible to display the loan schedule?

Many Many Thanks

I have a problem that is more of a math problem than excel.
I want to show an annual loan interest calculation that is based on a
monthly amortization schedule.
The problem is that each month we repay some of the principal and then next
months interest is calculated on a different principal amount.
Illustrated I am imagining that this is how this would look assuming we
start with a balance of $1,000 and an monthly interest rate of r.

1,000*r+(1,000-((1,000*r))*r+(1,000-(1,000-((1,000*r))*r)...and so on

This seems like some sort of math series and I am wondering if this can be
simplified in a formula.

If anyone can help I would really appreciate it.
Thanks.

Neda

Hi,I am trying to make a Pivot Table for my order form. So far what I have is everything showing up correctly except that the subtotals and grand total at the end do not show up at all.Does anybody know how I can fix my problem?When I set up the pivot table, I used data from a different spreadsheet, most of which is chosen through a drop down list. I created the pivot table using everything as row fields.I would appreciate help with this. Thank you.EDIT: I tried to draw up a table but it won't let me format it within this post with and without html. Basically the table has four categories: customer name, product name, quantity ordered, and price total. I would like the quantity ordered and price total to be subtotaled for each customer.

Hello,

I have a for each loop that exits when it shouldn't be. (Or so I think)

cRunSheets is a collection of the names of the sheets that are relevant to this procedure. Its a public variable.
I've validated it while the code was in break mode, there are 15 items in the cRunSheets collection. The loop should run 15 times.

Immediate window as the loop starts.

	VB:
	
?cRunsheets.Count 
15 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Basically, I want it to find the sheet I send the procedure (sentSheet) and clear the contents of that range on every subsqent sheet to the end of the collection, but nothing before the sheet it is sent. I am not a fan of using the sheet index, I would rather refer to it by name. There are many sheets in the workbook but my collection only gathers the ones I want.

getSheets calls the collection routine to populate from the workbook.


	VB:
	
) 
    Dim wsFlagSet As Worksheet 
    Dim parameter As Integer 
    Dim runSheet As Variant 
    Dim afterCurrent As Boolean 
     
    afterCurrent = False 
     'Sets parameter based on information passed. (Sheet = single sheet, forward = all sheets after)
    If IsMissing(sentSheet) = True And forward = False Then 
        parameter = 1 
    ElseIf IsMissing(sentSheet) = True And forward = True Then 
        parameter = 2 
    ElseIf IsMissing(sentSheet) = False And forward = False Then 
        parameter = 3 
    ElseIf IsMissing(sentSheet) = False And forward = True Then 
        parameter = 4 
    End If 
    Select Case parameter 
    Case 1 '//Nothing passed blow up everything
        For Each wsFlagSet In ActiveWorkbook.Worksheets 
            If Mid(wsFlagSet.Name, 1, 4) = "BRUN" Then 
                wsFlagSet.Range("H22:AK22").ClearContents 
            End If 
        Next wsFlagSet 
    Case 2 '//makes no sense but blow up everything here too.
        For Each wsFlagSet In ActiveWorkbook.Worksheets 
            If Mid(wsFlagSet.Name, 1, 4) = "BRUN" Then 
                wsFlagSet.Range("H22:AK22").ClearContents 
            End If 
        Next wsFlagSet 
    Case 3 '//Just a sheet passed, clear one sheet
        Sheets(sentSheet).Range("H22:AK22").ClearContents 
    Case 4 '//Sheet passed, forward passed clear sheet and everything to the end
        getSheets 
        For Each runSheet In cRunSheets 
             
            If runSheet = sentSheet Then 
                afterCurrent = True 
            End If 
             
            If afterCurrent = True Then 
                Sheets(runSheet).Range("H22:AK22").ClearContents 
            End If 
             
        Next runSheet 
         
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So this runs/compiles fine, I send it the 3rd sheet. It loops 1, 2 gets to 3 flips afterCurrent to True, executes the 2nd if block and clears the range. Then it goes to End Sub, always quits when afterCurrent gets flipped.

I feel silly for asking because this is programming 101, controlling loops and I just can't see what the problem is. As always, I am sure there is a different way to do this and I can work around it. However now it is an issue of prinicple and I have to figure out why this speciffic instance does not work because as written (to me) it should.

Pay no attention to the fact I used the workbook.sheets collection to loop on everything and then changed to my own collection... :D I'll be fixing that after I restore functionality...maybe.

Thanks in advance!!

I have an excel workbook which contains two sheets. Sheet 1 contains a list of items. I need to search for each of these items in sheet 2 and sum up the 'base price' and enter into column B of sheet 1. So basically

1. I need to pick up the first item in sheet 1.
2. Find the column which contains "Module Name" in sheet 2.
3. Search for the item picked up from sheet 1 in the column which contains "Module Name" in sheet 2.
4. Sum up the "Base Price" for all occurrences and enter this summed value into column B of sheet 1 next to each item.

I have no idea about how to even get started on this so i'd really appreciate some help.

I've attached the excel sheet to give a better idea of my query.

thanks

I want to apologize if this counts as repeating a previous thread, I feel that its a different branch of a smiliar topic. Anyways,

Attached you will find a sample file.

I am trying to have a for each loop that will look at each product and then go through each fund in the product and see which strat it is and add it to that and then see which sub strategy it is and add it to that.

For example,

Product 1, Fund 3 is part of Strat 1 and sub 1, so it should be added to product 1's total for strat 1 and sub strat 1, etc.

Any questiosn for clarification please ask.

Cheers

Hey All.

FYI this is a cross post from excelforums.

I'm having a hard time formulating an amortization schedule, where interest is calculated via rule of 78, not simple interest. AmorizeIT does it perfectly, however its not an excel sheet that i can see formulas in.

has anybody done thing before?

cheers

How do I calculate a loan amortization schedule for a loan that changes
interest rates after a few years?

I am using the loan amortization template available for excel. The loan I am
using is for 4.75 years (4 years 9 months). The template will not allow me
to use 4.75 years, it will only accept whole numbers between 1 and 30. Does
anyone have any suggestions on how I can get around this? Thank you!

I am trying to do a loan cashflow for 3 years and then extended over ten and
need 2 separate loan amortization schedules, but the second schedule won't
run separateky. It picks up detail from the first due to the formula format.
Any idea on how to override or what to do?

This should be a very simple procedure. I can't tell what is wrong. In the end the words "It worked" should populate to the far right. Not sure if I am incorrectly using the "concat" object variable in the IF statement. I'm not receiving any error messages. The hour glass does show after I run the macro, so something is happening. Can anybody offer some insight?

Sub populateSchI()
    
    Dim chargeNo As Range
    Dim concat As Range
    
    Set chargeNo = Worksheets("Schedule I").Range("A6")
    
    For Each concat In Worksheets("2008").Range("D:D")
        If concat.Value = chargeNo.Value Then
            concat.Offset(0, 7).Value = "It worked"
        End If
    Next concat
        

End Sub


Is there a template for a loan amortization schedule where the payments are
made with fixed monthly payments, but the interest is calculated on a daily
basis. I guess similar to a credit card.

I am looking for a template to plug-in the payment, loan amount, interest
rate and term for a loan to get a amortization table. Also if one of the
four above are missing it will calculate the missing number.

Hi there,

I have the following simple problem setup for two columns let's say A and B:

01-02-2011 -534
01-02-2011 53
01-02-2011 43
02-02-2011 25
02-02-2011 -3523
02-02-2011 532
02-02-2011 3

I need to calculate the absolute value for each date in the summing of column B.

for example if u sum the whole column B as it is above then you get -3401. What I want is not to get rid of this minus-sign. But i want to get rid of the minus for each date-sum, for instance:

01-02-2011 -534
01-02-2011 53
01-02-2011 43

the output/number i want from this is abs(-534+53+43)=438

then for

02-02-2011 25
02-02-2011 -3523
02-02-2011 532
02-02-2011 3

abs(25-3523+3253+532+3)=2963

so the total is 2963+438=3401

can any help? ... should i use for-each construction or is it not simpler??

thanks.


No luck finding an answer? You could always try Google.