Highline Excel Class 49: Spreadsheet To Compare Loans

Hi all,

I have to compare two spreadsheets, with same header rows and same number of column. Total number of row are different.

Please advice on the process to compare this two spreadsheet and get the difference on the third one. Can we create a macro to solve this?? Please suggest,

Thanks,
Amit Jain

I have over 65,000 entries in one column. I might have to use MS Access for
this

I am looking for a macro to compare two excel spreadsheets. On Column C on both spreadsheets it has a column name called ALU. I need the Macro to match ALU (data below the alu column) for each spreadsheet and if it matches to copy the cost and price over to to the 2nd spreadsheet based on the match of the ALU. The cost and price are on both spreadsheets with the same column names, column f (cost) and column g (price). The only other thing thats really important is to only copy across the cost/price if they aren't a blank or a 0.

Example of alu on each row - fan-venomx, kb-mk200, kb-gkg19
Example of Cost on each row - 53.00, 62.00, 176.00 and so on
Example of Price on each row - 92.35, 82.19, 203,12 and so on

i know this is kind of confusting, if you need more clarification or a sample of the spreadsheet please let me know?

thanks in advance

I need a little help...
I have 2 excel files. the first one (let's name it original_data) has Last_Name, First_Name, Address and some other fields (ex: Make). The 2-nd file (let's name it Scrubed_Data) is created from the first one, after some names have been removed according to some criteria which doesn't matter. But when the names have been removed, the field Make was removed also from the Scrubed_Data. I need the field Make.
I thought of 2 ways of having the field in my file:

1. I need to compare the Scrubed_Data against the Original_Data and if the names from the original_data can't be retrieved in the Scrubed_Data, delete the entire row.
Or,
2. I thought it would be easyer to create an Array formula in the scrubed_data file that finds the make from the original_data for the combination of Last_name, First_name & Address. In the Scrubed_Data I'm using a formula as follows:

colmake = 9
collast = 2
colfirst = 1
coladdress = 4
For i = 1 To 3
Cells(i, colmake).FormulaArray = "=INDEX(Sheet2!B:B,MATCH(" & _
"""" & Cells(i, collast) & Cells(i, colfirst) & Cells(i, coladdress) & """" & _
",Sheet2!B:Sheet2!A:Sheet2!C:C,0))"
Next

(where in Sheet2 I have copied the records from the original_data)
but i get the following message:
"Unable to set the FormulaArray properly of the Range class".

Can somebody, please, help?

I would like to compare multiple loans with and without balloon payments.
The common is loan amount $500,000. # compounding periods/yr, and balloon pmt option $100,000.
EX:
Option# Down Payment Nominal Interest Rate/yr Duration (yrs) Points Fees Loan Value Monthly Payment Actual Amount Borrowed APR Payment with Balloon 1 15.00% 8.00% 30 2 450 425,000.00 -3,097.85 416,050.00 -7.82% -$3,164.50 2 18.00% 7.95% 25 1.75 375 410,000.00 -3,130.14 402,450.00 -9.97% -$3,235.45 3 17.50% 9.15% 20 2.5 295 412,500.00 -3,722.87 401,892.50 -12.52% -$3,868.65 4 25.00% 7.50% 25 0 0 375,000.00 -2,754.00 375,000.00 -10.26% -$2,867.29 5 20.00% 6.95% 20 0 0 400,000.00 -3,071.41 400,000.00 -13.63% -$3,263.44 What throws me off is the "4 compounding periods/yr" and why is the payment more with a $100,000 balloon at the end. Mathematically it doesn't make sense in my mind. Using the PMT function, with the FV being 100,000.
Any help would be appreciated

How can I amortize more than one loan on a daily basis in order ?
to consolidate total liabilities ( Total loans ) , Monthly payment should be made to lender , Total interest expense to be recognized out of these loans ? total principle payment to be deducted from total liability taken? , can you please support me with the required schedules for that and explain to me how to use


Best regards,

Hi,

I am a cabinet maker who needs to borrow 30,000 from a friend because I

tweaked my back for a few weeks and now have to catch up for the next
few months. I want to be able to make a worksheet that allows me to
give him a known interest rate and then apply the rest to the
principal. I would love it if I could do it on any day of the month
and not have it tied to a monthly period. It is essentially exactly
what credit cards do. Ideally, it would show in columns what was paid,

what day it was paid, how much went to interest for that month, how
much went to principal, and how much principal remains. Incidentally,
this would be great for just capital borrowing in general. The lender
gets a fixed return, but I can write checks willy nilly. As an added
tweak (just like the credit cards) if I don't make the interest in a
month, I would love for the unmet interest to roll to the next month.


Any ideas?


Wouldn't it be cool if the lender could access this info on my webpage
via a password!


Thanks...Patrick...santafedisp...@hotmail.com


PS If anyone could set this up I would be willing to pay something via

check or paypal.

This may be asking for a miracle, but at this point a miracle is the only hope I have left. I have to answer the following question using Excel, and I don't have the slightest clue. Any help would be appreciated beyond belief.


Question:
1) 100,000,000 portfolio of loans

2) 9% interest rate/annum

3) 0.5% cost to service the loans/annum

4) 10% voluntary prepayment/annum (10% of all people repay their loans each year)

5) 10% default/annum, but 50% of all defaulted monies is recovered

6) 360 month Amortization


The question is: What is the total cash flow from the loans?




I will give my life to anybody that can help. Thank you in advance.


Sincerely,
ErniePoe

Hi all

Not been able to find the answer to this question in previous posts, and wondered if anyone could help:

I am using the PMT function to calculate quarterly loan repayments. I have this formula in cell D51:

=PMT(D53/4,D54*4,-D50)

where
D53 = Interest rate %
D54 = Loan term in years
D50 = Loan amount

What I need is a similar formula that I can put in cell D50 to tell me the total loan amount possible, if I vary the value in D51.

I can find this info using the Goal Seek function, but I would like to use a formula here as it is more convenient in the tool I am putting together.

Any advice greatly received.

Thanks